Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Thad
Frequent Visitor

Display movement over time in a matrix

Hello,

 

I am, so far unsuccessfully, trying to display the movement of group membership throughout time in a matrix.

 

My source is a table of 3 columns, "Stichtag" (Date of occurence), "Personennummer" (Customer ID) and "VR-Rating" (Group they belong to at the time), for which I created lookup tables named "Stichtag Aktuell", "Stichtag Vergleich", "Rating Aktuell" and "Rating Vergleich" with their respective distinct in values in them. The aim is to have a matrix that shows how many people moved groups (also from which to which) and how many stayed in their respective groups between two selected dates. Like this:

 

image.png

 

When I try it with this measure:

Rating =
VAR ST_Akt = SELECTEDVALUE(Stichtag_Aktuell[Stichtag])
VAR ST_Vgl = SELECTEDVALUE('Stichtag Vergleich'[Stichtag])
VAR VR_Akt = SELECTEDVALUE(Rating_Aktuell[VR-Rating])
VAR VR_Vgl = SELECTEDVALUE(Rating_Vergleich[VR-Rating])
 
RETURN CALCULATE(DISTINCTCOUNT(Rating_Bewegung[Personennummer]), FILTER(Rating_Bewegung, Rating_Bewegung[Stichtag] = ST_Akt && Rating_Bewegung[Stichtag] = ST_Vgl && Rating_Bewegung[VR-Rating] = VR_Akt && Rating_Bewegung[VR-Rating] = VR_Vgl))
it won't show any data because the filters cancel each other out. When I split it into two measures, calculating the values for each date seperately, it displays the values like this:
Thad_0-1705926335693.png

I know I would still have to calculate the actual movement as right now I just have the amount of members as my output but my first goal would be to get all the data in a matrix that has the same amount of columns as rows. What am I doing wrong? I am sure there is a simple solution to this. Is there another visual I could use? 

 

Thanks in advance

1 REPLY 1
v-binbinyu-msft
Community Support
Community Support

Hi @Thad ,

Sorry, so far, to my knowledge, this may not be achieveable.

 

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.