Forum Discussion

Anderrated's avatar
Anderrated
Frequent Visitor
3 years ago
Solved

Create new table using dynamic measure to filter

So, I am fairly new to PowerBI, i tried to understand similar topics to my issue but i can't make it work. My case is the following:   I have TableA which can be summarized down to two columns:   ...
Need Help
Tips and Tricks
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    Hi, Anderrated 

    Thanks for your quick response and provided table . Yes, you want to use the slicer's date into the calculated table, it will not work.

    For your need , you need to first to create a Date table as a slicer, like this:

    Date = CALENDAR(FIRSTDATE('TableA'[Date]),LASTDATE('TableA'[Date]))

     

    Then you need to make the relationship like this:

    Now, we can use the 'Date'[Date] as a slicer to filter 'TableB'  and we can use the 'TableA' as 'TableC' you want to get . 

     

    We can create a measure:

    Measure = var _slicer_date = MAX('Date'[Date])

    var _cur_date = MAX('TableA'[Date])

    return

    IF(_cur_date> _slicer_date ,1,0)

     

    Then we can put the measure on the visual (‘TableA’),like this:

    Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

     

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    Hi , Anderrated 

    The "var _cur_date = MAX('TableA'[Date])" return the Date every row in Matrix .You can create a measure to test like this:[The measure is calcualteing in the current context filter!]
    Measure= MAX('TableA'[Date])

     

    And the "MAX('Date'[Date])" , your understand is right~

     

    Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

     

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

     

Anderrated's avatar
Anderrated
Frequent Visitor
3 years ago

Hi Aniya, it worked!!!, that was very clever solution! I have one more question, it is working but i would like to understand.

 

Measure = var _slicer_date = MAX('Date'[Date]) ----> the maximum of Date i understand is the slicer selected max date, in your example is 30/06/2022

 

var _cur_date = MAX('TableA'[Date]) --> the maximum value of TableA, which is 01/12/2022

return

IF(_cur_date> _slicer_date ,1,0)

 

Then the IF statement is IF(01/12/2022>30/06/2022) return a 1 if not return a 0. But isn't it always true? what i see it happens is that it asigns a 0 on the dates < than 30/06/2022 and a 1 to the dates > 30/06/2022. but i dont understand why. Shouldn't it be always 1?¿

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    Community Support
    3 years ago

    Hi , Anderrated 

    The "var _cur_date = MAX('TableA'[Date])" return the Date every row in Matrix .You can create a measure to test like this:[The measure is calcualteing in the current context filter!]
    Measure= MAX('TableA'[Date])

     

    And the "MAX('Date'[Date])" , your understand is right~

     

    Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

     

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly