Forum Discussion

Springbook89's avatar
Springbook89
Frequent Visitor
8 years ago
Solved

Displaying unique values in a table depending on two slicers (different periods)

Hi there,

 

I am struggeling with a visualization in Power BI and would be glad on your help.

 

Basicially I want to vizualizse a table that displays values that are unique in a given column for a selected/filtered period relative to another selected/filtered period. Currently I have basicially have created two slicers (Current selected period and comparing period).  

 

Now the raw data looks like this:

 

Period

Code

15.01.2017

A123

15.01.2017

B123

15.01.2017

C123

31.10.2017

A123

31.10.2017

F123

22.02.2018

F123

 

Basicially my goal is to select for example 31.10.2017 via one of the slicers as the current period and with another slicer the 15.01.2017 as a reference period. Now I need a table that displays me the unique Cods in the current period. In this case F123 (as this is not existing in period 15.01.2017, as A123 is already existing in 15.01.2017 and B123 and C123 are not existing in 31.10.2017 anymore). Now if I change the values in the slicers, also the table should update. 

 

Would you have some ideas around that? Happy to provide additional info, if it is not 100% clear.

 

All the best,
Renê

Need Help
Show and Tell
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Springbook89

     

    Now you can use this MEASUE to get desired results

     

    Measure =
VAR MainTable =
    CALCULATETABLE (
        VALUES ( TableName[Code] ),
        FILTER (
            ALL ( TableName ),
            TableName[Period] = SELECTEDVALUE ( Table1[Period] )
        )
    )
VAR ReferenceTable =
    CALCULATETABLE (
        VALUES ( TableName[Code] ),
        FILTER (
            ALL ( TableName ),
            TableName[ReferencePeriod] = SELECTEDVALUE ( Table2[ReferencePeriod] )
        )
    )
RETURN
    CONCATENATEX ( EXCEPT ( MainTable, ReferenceTable ), [Code], ", " )

     

6 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    8 years ago

    Hi Springbook89

     

    See the attached file here

     

    Following are the steps

     

    1) Added a duplicate Column "Reference Period"

     

    ReferencePeriod = TableName[Period]

    2) Created 2 Separate Calculated Tables...(to be used as slicer)

    Table1 = All(TableName[Period])

Table2 = All(TableName[ReferencePeriod])
    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Icon for Community Champion rankCommunity Champion
      8 years ago

      Springbook89

       

      Now you can use this MEASUE to get desired results

       

      Measure =
VAR MainTable =
    CALCULATETABLE (
        VALUES ( TableName[Code] ),
        FILTER (
            ALL ( TableName ),
            TableName[Period] = SELECTEDVALUE ( Table1[Period] )
        )
    )
VAR ReferenceTable =
    CALCULATETABLE (
        VALUES ( TableName[Code] ),
        FILTER (
            ALL ( TableName ),
            TableName[ReferencePeriod] = SELECTEDVALUE ( Table2[ReferencePeriod] )
        )
    )
RETURN
    CONCATENATEX ( EXCEPT ( MainTable, ReferenceTable ), [Code], ", " )

       

      • Springbook89's avatar
        Springbook89
        Frequent Visitor
        8 years ago

        Perfect. Many thanks for that! A had the same approach with the slicers, but could not figure out a good measure function. Top, thanks! :)