Want to represent this data as follows in a table visualization

[Anonymous]

I have the data as follows:
This is how I have the dataThis is how i want the data to be represented in my visualizationAll of them are in a single table. How should i model the data or transform it so that I can achieve the desired result

[Icey · Accepted solution]

Hi @Anonymous ,

I optimized the above method. And this is my PBIX file.

You can create your columns and measures like so:

columns:

Car = 
CALCULATE (
    MAX ( 'Table'[Name] ),
    FILTER ( ALLEXCEPT ( 'Table', 'Table'[car ID] ), 'Table'[Type] = "Car" )
)

Parts = 
IF (
    'Table'[Type] = "Parts",
    CALCULATE ( MAX ( 'Table'[Name] ), ALLEXCEPT ( 'Table', 'Table'[Parts ID] ) ),
    BLANK ()
)

measures:

Completion Date Measure = 
SWITCH (
    TRUE (),
    AND ( ISINSCOPE ( 'Table'[Parts] ), ISBLANK ( VALUES ( 'Table'[Parts] ) ) ), BLANK (),
    CALCULATE (
        MIN ( 'Table'[Completion Date] ),
        ALLEXCEPT ( 'Table', 'Table'[Car], 'Table'[Parts] )
    )
)

Owner Name Measure = 
SWITCH (
    TRUE (),
    AND ( ISINSCOPE ( 'Table'[Parts] ), ISBLANK ( VALUES ( 'Table'[Parts] ) ) ), BLANK (),
    MAX ( 'Table'[Owner Name] )
)

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I am using the Matrix visualization and have added Cars and Parts to the ROW section. Now I want the entire table to be filtered by 2 slicers. The values for them are contained in 2 separate tables. All the relationships have been set up correctly. When I try to slice the matrix using those filters all the other columns except the first one (Cars and Parts) filters. So basically I have all the values for column 1 and then for the rest of the columns, the values will appear as per the value selected in the slicer.

I would want the slicer selection to affect the first row as well and only display the values for the selection made. Can you please help me out with that

[Anonymous]

Thanks a lot. This solution worked for me!!!! 🙂

[Icey]

Hi @Anonymous ,

I can only get the result like below now. This is my PBIX file.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.