Forum Discussion

tez's avatar
tez
Resolver I
6 years ago

Date parameter for DAX table comparison

Hi!

I have to create a table comparison within Power BI. Here´s demo data for better understanding. I have one table which consists of a number of IDs for some timestamps:

I have to create a comparison for two timestamps, which shows, which IDs are recorded on one timestamp but not the other, and which IDs are recorded on both timestamps. For this I have created a calculated column which allows to filter:

 

 

Intersection = 
    VAR Date1 = DATE(2020,4,1)
    VAR Date2 = DATE(2020,5,3)
    VAR Table1 = FILTER(TestTable, [Date]=Date1)
    VAR Table2 = FILTER(TestTable, [Date]=Date2)
return IF(AND([Date]<>Date1,[Date]<>Date2),"irrelevant",
        IF(NOT(CONTAINS(Table1, [ID], [ID])), "not included in Date1",
        IF(NOT(CONTAINS(Table2, [ID], [ID])), "not included in Date2",
        "included in Date1 and Date2")))

 

 

This seems to work well, but I need to get the two timestamps as parameter, selectable by the user. The user should be able to select an existing timestamp, not to enter any date (instead of hard coded variables in line 2 and 3 above). How can this be done? Is there any more elegant way to do the comparison?

Attached (corrected link) you´ll find the pbix file. Any comments are appreciated.

Regards,

Thomas

2 Replies

  • stevedep's avatar
    stevedep
    Memorable Member
    6 years ago

    Hi,

     

    Perhaps the following is what you are looking for. This will check per code if the number of dates is equal to the number of selected dates, if so they appear in both (or more).

     

     

     

    PresentInBoth = 
var NumberOfDates = CALCULATE(DISTINCTCOUNT(Table1[Date]);all(Table1);ALLSELECTED(Table1[Date]))
return 
CALCULATE(DISTINCTCOUNT(Table1[Date])) = NumberOfDates

     

     

     

    Kind regards,

     

    Steve. 

     

    Please accept as solution if this is what you are looking for.

    • tez's avatar
      tez
      Resolver I
      6 years ago

      Hi stevedep,

      thank you for this hint, but I could not manage to get a solution. It seems to me, that it is not possible to use this context, because either it filters the target table in a way not intended, or it does not work at all.

      Regards,

      Thomas.