none
Include month from DIM Date table that have matching records in Fact table RRS feed

  • Question

  • Hello!

    I have a cube where I have a fact and date tables. I have established the relationship between them so that I can perform time intelligence functions. In power BI report, I have used "year" from Date table as a slicer. The problem is every single year in the DIMDate table is appearing in that slicer. I want it, so it only displays years that match with the data that is the Fact table. Is it possible?

    Regards

    PV

    Wednesday, November 6, 2019 3:50 PM

Answers

  • My guess would be that "Value" is a column, not a measure. If you create a measure over your fact table or a column in it this technique should work. It does not matter what the measure is, either a SUM or a COUNTROWS base measure would work fine. 

    eg.

    Total Value = SUM( Fact[Value] )

    Fact Rows = COUNTROWS( Fact )


    http://darren.gosbell.com - please mark correct answers

    Friday, November 8, 2019 10:57 PM
    Moderator

All replies

  • Hello!

    I have a cube where I have a fact and date tables. I have established the relationship between them so that I can perform time intelligence functions. In power BI report, I have used "year" from Date table as a slicer. The problem is every single year in the DIMDate table is appearing in that slicer. I want it, so it only displays years that match with the data that is the Fact table. Is it possible?

    Regards

    PV

    Hi PV,

    Yes, it is possible. But it depends on your business design. If you could create date-month-year hierarchy structure in the date dimension, it would be better.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 7, 2019 3:06 AM
  • Just add a filter to your slicer that checks for a value in one of the measures in your fact table. So if it was an orders fact you could filter your year slicers for [Order Count] > 0

    http://darren.gosbell.com - please mark correct answers

    Thursday, November 7, 2019 4:34 AM
    Moderator
  • Hi Will Can you explain more about it?

    Regards

    PV

    Thursday, November 7, 2019 2:07 PM
  • Hello Darren

    I tried your solution but it has no effect. Filter still shows all year values in the slicer.

    Regards

    PV

    Thursday, November 7, 2019 2:24 PM
  • I tried your solution but it has no effect. Filter still shows all year values in the slicer.

    So that either means you have data for all those years or there is something wrong with the relationship between the fact and dim.

    When you drag the year and the measure you used into a table does it show different values for all the years? Or does it show the same value for every year (if the relationship between the dim and fact is not present or is in the wrong direction the grand total will repeat for every row) 


    http://darren.gosbell.com - please mark correct answers

    Thursday, November 7, 2019 7:23 PM
    Moderator
  • Hello Darren

    Thank you for your reply. This is what it looks like. I dont know where I am going wrong.

    Regards

    PV

    Friday, November 8, 2019 2:16 PM
  • My guess would be that "Value" is a column, not a measure. If you create a measure over your fact table or a column in it this technique should work. It does not matter what the measure is, either a SUM or a COUNTROWS base measure would work fine. 

    eg.

    Total Value = SUM( Fact[Value] )

    Fact Rows = COUNTROWS( Fact )


    http://darren.gosbell.com - please mark correct answers

    Friday, November 8, 2019 10:57 PM
    Moderator
  • Thank you Darren! that worked.
    Wednesday, November 13, 2019 1:52 PM