Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
In the following example there is a list of rules. I want them to be displayed in a matrix under the date that they are related to. What I mean by that is that the matrix should show dates at the top then all of the rules associated with that date in a list below it. Currently it is only displaying the first. Is there a way display all of them? Thank you.
Here are some related screenshots.
Solved! Go to Solution.
@cmckinney - You could use CONCATENATEX to concatenate all of the rules:
Measure = CONCATENATEX('Table',[Rule],",")
But maybe put Date and Rule columns in a hierarchy on rows would be better?
@cmckinney - You could use CONCATENATEX to concatenate all of the rules:
Measure = CONCATENATEX('Table',[Rule],",")
But maybe put Date and Rule columns in a hierarchy on rows would be better?
Thank you for the reply. I tried the CONCATENATEX, as per your direction, and found that it wont work for this situation. The reason being is that I need each to be an individual cell so that I can show if there has been a change from one date to the next. Maybe putting all of the rules on one column isn't the play in this situation.
I was thinking that it might just be better to put the rules in the rows and the dates in the columns, like this:
THEN create a measure showing wheter or not the rule has changed from one date to the next. There is a column in the data source that is called "Hashed Value". When that rule changes, so does the hashed value. This essentially is the flag that we need to show has changed in the Measure that will be put into the values in the matrix. Does that make sense? Please let me know if you have any more questions.
Bellow shows the hashed value column in the data:
@cmckinney Yes, I was actually thinking the same thing when I was responding but was trying to "answer the mail"!
@Greg_Deckler Thanks!
Could you by chance show me how to make a measure that would go in the values portion of the matrix that shows whether or not there was a change in the "Hashed Value" column?
I will accept your first response as a solution because it did answer what my question originally was.
@cmckinney - If I am understanding the situation correctly, maybe something like:
Measure =
VAR __Rule = MAX([Rule])
VAR __CurrentDate = MAX([Analysis Run])
VAR __CurrentHash = MAX([Hash])
VAR __PreviousDate = MAXX(FILTER(ALL('Table'),[Analysis Run]<__CurrentDate),[Analysis Run])
VAR __PreviousHash = MAXX(FILTER(ALL('Table'),[Analysis Run]=__PreviousDate && [Rule]=__Rule),[Hash])
RETURN
IF(__PreviousHash <> __CurrentHash,1,BLANK())
That's a best guess, let me know if that doesn't fit the bill.
@Greg_Deckler Awesome! This is so close to working! Here is a screenshot of what I see. I just need the first column to show 0s and not 1s. Does that make sense? How do I modify the DAX so that it will always display that first row as 0?
@Greg_Deckler I forgot to mention this, I added an if statement before the end but I have since removed it.
@cmckinney - OK, I think:
Measure =
VAR __Rule = MAX([Rule])
VAR __CurrentDate = MAX([Analysis Run])
VAR __CurrentHash = MAX([Hash])
VAR __PreviousDate = MAXX(FILTER(ALL('Table'),[Analysis Run]<__CurrentDate),[Analysis Run])
VAR __PreviousHash = MAXX(FILTER(ALL('Table'),[Analysis Run]=__PreviousDate && [Rule]=__Rule),[Hash])
RETURN
SWITCH(TRUE(),
ISBLANK(__PreviousDate)||__PreviousDate<0||YEAR(__PreviousDate)<1900,0,
ISBLANK(__Rule),0,
__Rule = "UnusedRule1",0,
__PreviousHash <> __CurrentHash,1,BLANK(),
2
)
@Greg_Deckler okay so, this is getting so close to what I need. I modified your DAX a little bit. Here is what I have so far:
Measure =
VAR __Rule = MAX([Rule])
VAR __CurrentDate = MAX([Analysis Run])
VAR __CurrentHash = MAX([Hashed Value])
VAR __PreviousDate = MAXX(FILTER(ALL('analysis vAnalysisRunRuleDetails'),[Analysis Run]<__CurrentDate),[Analysis Run])
VAR __PreviousHash = MAXX(FILTER(ALL('analysis vAnalysisRunRuleDetails'),[Analysis Run]=__PreviousDate && [Rule]=__Rule),[Hashed Value])
RETURN
SWITCH(TRUE(),
ISBLANK(__PreviousDate), 0,
ISBLANK(__Rule), 0,
__PreviousHash <> __CurrentHash, 1,
BLANK(),2
)
@cmckinney Sure, happy to help. So, just to be clear, there is still an issue or no? Sorry, wasn't sure if you were 100% happy or not yet.
Yes, there is still an issue. The issue is that the measure needs to be dependent on the matrix and not the table. Meaning the conditional format works according to what is displayed currently on the matrix. Does that make sense? Currently it is formatted according to what is in the data itself and not what is displayed on the matrix.
Okay, so at this point this thread/what I am trying to accomplish have become convoluted and confusing. The following is as boiled down and concise as I think I can make my ideas. Hopefuly it helps clear things up.
Here is a sample file that I think will help. https://www.dropbox.com/s/gsaucwi6iqj1upv/RulesConditionalFormatDummy.pbix?dl=0
The end goal is to plug the measure into a conditional format on the matrix.
The product of the measure should be a 0 , 1 or 2.
1 = If the hashed value has changed from one rule to the next.
2 = If the rule is new
0 = If there is no change and the rule is not new
In the conditional format 1 will be red, 2 will be blue and 0 will remain black.
The first column in the matrix must always remain black.
Here is a screenshot of the sample file I made. Notice, the second column is blue. It shouldn't be blue because the previous hash value is not blank and has the same hash value from the previously displayed date.
The only two that should be formatted and are formatted correctly are the red 'hashchange' and the blue 'newrule'.
When the 'Combined Test' publish set is selected the colum turns blue. This shouldn't happen because it is the first column in the table.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |