Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Springbook89
Frequent Visitor

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ê

1 ACCEPTED SOLUTION

@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], ", " )

 

Unique2filters.png


Regards
Zubair

Please try my custom visuals

View solution in original post

6 REPLIES 6
Zubair_Muhammad
Community Champion
Community Champion

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])

Regards
Zubair

Please try my custom visuals

@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], ", " )

 

Unique2filters.png


Regards
Zubair

Please try my custom visuals

Hi Zubair, @Zubair_Muhammad 

 

I have something very similar to this.  In one excel sheet, sheet1, I have date and product.  When a user selects two different dates, I want to show what is different between the two dates with regards to Products sold.  For example, if the date chosen is 8/2 and 8/4, it should show R5 product.  Could you please help me?  Thank you so much

 

Date                     Product

8/1/2020             R1

8/1/2020             R2

8/1/2020             R3

8/2/2020             R1

8/2/2020             R2

8/2/2020             R3

8/2/2020             R4

8/4/2020             R1

8/4/2020             R2

8/4/2020             R3

8/4/2020             R4

8/4/2020             R5

@Zubair_Muhammad

 

Many thanks again for your answer. 

 

It is working fine, so that all new codes are concatenated (e.g. F123, G123,...).

 

However, how to I need to transform the return function if I want to have that as a column/list that just displays all the new codes in a list/column format?

Code new

F123
H123

 

 Best 🙂

 

PS: Basicially, I want to display a table of "new codes" (you can also envision "new clients") based on the two slider values (Period and ReferencePeriod). The actual table I have has more columns, so that I can later on also add additional columns to the "new codes" giving information on client names or sales quantity etc.

@Springbook89

 

You can create a calculated Table from the Modelling Tab..... But unfortunately it wont be dynamic....i.e. it won't react to SLICER selection from another table

 

Table =
VAR MainTable =
    CALCULATETABLE (
        VALUES ( TableName[Code] ),
        FILTER ( ALL ( TableName ), TableName[Period] = "___" )
    )
VAR ReferenceTable =
    CALCULATETABLE (
        VALUES ( TableName[Code] ),
        FILTER ( ALL ( TableName ), TableName[ReferencePeriod] = "___" )
    )
RETURN
    EXCEPT ( MainTable, ReferenceTable )

Regards
Zubair

Please try my custom visuals

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.