Changing a value in matrix to show all rows and not just first

[cmckinney]

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.

[Greg_Deckler · Accepted 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]

Hi @Greg_Deckler 

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:

[Greg_Deckler]

@cmckinney Yes, I was actually thinking the same thing when I was responding but was trying to "answer the mail"!

[cmckinney]

@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.

[Greg_Deckler]

@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. 

[cmckinney]

@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?

[cmckinney]

@Greg_Deckler I forgot to mention this, I added an if statement before the end but I have since removed it.

[Greg_Deckler]

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

[cmckinney]

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

The expression was not working with the "__PreviousDate<0" so I dropped it. It appears to be working correctly I am just running into a separate issue now.

Let me back up a little for a second and hopefully I can more clearly define the scope. What is needed is for 3 different integers to be produced from this measure. 1 if the hash that is associated with the rule has changed from the previous date that is currently displayed in the matrix. 2 (this one is new and I haven't mentioned yet) if the rule is new in relation to the displayed dates in the matrix. And finally a 0 if neither of the previous statements are true. I will be using these numbers to create a conditional format applied to the values currently displayed in the matrix. 1 will be red, 2 will be blue and 0 will stay black.

The measure is working when applied to the matrix as a conditional format in that it is turning all of the values red that have different hashes from the previous date in the data table itself. It needs to be applied only to what is currently showing in the matrix. Does that make sense? So no matter what the first column in the matrix will always be black then the next column will compare it's hashes to the previous column in the matrix and NOT the previous date in the data.

Please let me know if you have any more questions and thank you again for all of the help. I am fairly new to Power BI and you are helping me a tremendous amount.

Here is a screenshot of how far we have come: 

[Greg_Deckler]

@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.

[cmckinney]

@Greg_Deckler 

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. 

[cmckinney]

@Greg_Deckler 

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.