Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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ê
Solved! Go to Solution.
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], ", " )
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])
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], ", " )
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
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.
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 )
Perfect. Many thanks for that! A had the same approach with the slicers, but could not figure out a good measure function. Top, thanks! 🙂
User | Count |
---|---|
125 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |