Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
Hello,
I'm trying to create a mesure/column (whatever works), to retrieve a comparison value between columns in a matrix visual.
I have 4 slicers in total and in each slicer has the same date field.
- This Date field comes from a dummy,
- Meanwhile the values I want to compare come from my fact table. These values are strings, not numbers.
The two tables (this dummy and the fact table) are disconnected and can't be in a relationship.
Because my user can select a maximum of 4 values, this is what I did:
I created this dummy table where I have two columns: Index and Date
- Column Date is nothing more than the distinct value of my Date in the fact table
- Column Index is basically generated for each distinct value of of my Date
This is the result:
IndexREFERENCEDATE_DT
1 | 31/03/2022 |
1 | 30/06/2022 |
1 | 31/12/2021 |
1 | 31/12/2022 |
2 | 31/03/2022 |
2 | 30/06/2022 |
2 | 31/12/2021 |
2 | 31/12/2022 |
3 | 31/03/2022 |
3 | 30/06/2022 |
3 | 31/12/2021 |
3 | 31/12/2022 |
4 | 31/03/2022 |
4 | 30/06/2022 |
4 | 31/12/2021 |
4 | 31/12/2022 |
Then I created 4 slicer objects in my page, and each slicer is filtered by its corresponding Index number.
Example for one of the slicers:
I achieved this in order to work with my Index field, since the user can select whatever they want in any order they want.
What I'm really struggling with is to create a measure/column than can compare the values after the selection.
So, for example:
Date_1 Date_3
Value RED Value GREEN
..where, Date 1 is my first selection, Date 3 is my second selection. And this can happen in any order, not chronological.
So, Value RED is worse than Value GREEN so my flag is negative .
Something like that...
I tried EARLIER function, but the two tables must be in a relationship, as far as I understand.
I also tried the function OFFSET, but it goes back of a specific number and this can't work because the order of selection isn't always Index - 1.
Please, if you any idea, any suggestion/opinion.. anything is very well accepted.
Thank you so much
Solved! Go to Solution.
Hi, thank you for your suggestion but unfortunately I can't use more than one measure since my visual is matrix type.
Is there any way to use one measure/column instead of four?
Thanks anyway.
@MCacc , Seem like you want compare two dates and two index , 1 set of each. You will need 4 disconnected tables
//Date1 and Index 1 is independent tables
new measure =
var _date= maxx(allselected(Date1),Date1[Date])
var _index= minx(allselected(Index1),Index1[Index])
return
calculate( sum(Table[Value]), filter('Table', 'Table'[Date]= _date && 'Table'[Index] = _index ))
//Date2 and Index 2 is independent tables
new measure =
var _date= maxx(allselected(Date2),Date2[Date])
var _index= minx(allselected(Index2),Index2[Index])
return
calculate( sum(Table[Value]), filter('Table', 'Table'[Date]= _date && 'Table'[Index] = _index ))
Power BI Abstract Thesis: How to use two Date/Period slicers
Compare Categorical Data Using Slicers - Compare two Brands: https://youtu.be/exN4nTewgbc
Hi, thank you for your suggestion but unfortunately I can't use more than one measure since my visual is matrix type.
Is there any way to use one measure/column instead of four?
Thanks anyway.
User | Count |
---|---|
81 | |
75 | |
70 | |
42 | |
36 |
User | Count |
---|---|
114 | |
56 | |
52 | |
43 | |
42 |