Forum Discussion
How to summarise and transpose (?) data
Hi SteffanH ,
Believe there is no need to create a new sumarization table. Try the following steps:
- Add a new table with the status (without any relationship to the other table)
- Create the following two measure:
Current = CALCULATE(COUNT('Values'[Column]);FILTER('Values';'Values'[Current Value]= SELECTEDVALUE('Status'[Status]))) + 0 Previous = CALCULATE(COUNT('Values'[Column]);FILTER('Values';'Values'[Previous value]= SELECTEDVALUE('Status'[Status]))) + 0- Then use this on your two visualizations
Other option to go is:
- Unpivot the Current Value and Previous value columns
- Create a status table with relationship active to the first table
- Create this two measures:
CurrentUnpivot = CALCULATE(COUNT('Values (Unpivot)'[Column]);'Values (Unpivot)'[Type] = "Current value") + 0 PreviousUnpivot = CALCULATE(COUNT('Values (Unpivot)'[Column]);'Values (Unpivot)'[Type] = "Previous value") + 0- Create your visualizations normally.
Check PBIX file attach.
Super UserHi SteffanH
Regarding the question about the dot comma and comma is regional settings related, depending on the settings of your computer the function parameters are determined by dot comma or comma. 😀
If you went to the first option that is a non-related table that is correct, since you don't have a relationship you will not get filtering information.
Was afraid of the regional settings thing being the cause.
Filtering should only be needed/applied on the "current" value, I assume the existing measures will not break if I create the relation between Current value and the new Status table.
- MFelix
Hi SteffanH ,
You can do two relationship one active on current value and one inactive on previous then change your measures to:
Current = COUNT('Values'[Column]) Previous = CALCULATE(COUNT('Values'[Column]);USERELATIONSHIP('Values'[Previous value]; Status{[Status])) + 0Should work as expected and then the filtering will be applied.