Create a table with unrelated measures in a single column

[valima]

Hi,

We have a requirement to have a table with unrelated measures to be displayed but formatted similarly to other tables on the report. So basically we need a table like the below:

Worst Loss	$1,00,000
Start Month	Jan-24
End Month	May-24
Duration	5 months

What would be the best way to build something like this? The values showns would come from measures.

Thanks for your help in advance

[dharmendars007 · Accepted solution]

Hello @valima , 

It Looks complicated but for a row reference you can create a Switch logic to achive this but it also depends on how complex your logic is..you can take some reference from the below dummy code.

MeasureValues =
SWITCH(TRUE(),
SELECTEDVALUE(MeasureNamesTable[Measure Name]) = "Worst Loss", [WorstLoss],
SELECTEDVALUE(MeasureNamesTable[Measure Name]) = "Start Month", [StartMonth],
SELECTEDVALUE(MeasureNamesTable[Measure Name]) = "End Month", [EndMonth],
SELECTEDVALUE(MeasureNamesTable[Measure Name]) = "Duration", [Duration])

If you find this helpful , please mark it as solution and Your Kudos/Likes are much appreciated!

Thank You

Dharmendar S

LinkedIN 

[Anonymous · Accepted solution]

Hi @valima 

Since I don't know what your data looks like, I'll use the following simple data example and hope it helps.

The sample data is this:

1. Create the following measures:

Worst Loss = FORMAT(MAX('Table'[Loss]), "$#,##0")

Start Month = FORMAT(MIN('Table'[Date]), "MMM-yy")

End Month = FORMAT(MAX('Table'[Date]), "MMM-yy")

Duration = CONCATENATE((DATEDIFF([Start Month], [End Month], MONTH) + 1), " months")

2. Create a new table that combines all measures into one table.

SummaryTable = 
UNION(
    SELECTCOLUMNS(
        ROW("Measure", "Worst Loss", "Value", [Worst Loss]),
        "Measure", "Worst Loss", "Value", [Worst Loss]
    ),
    SELECTCOLUMNS(
        ROW("Measure", "Start Month", "Value", [Start Month]),
        "Measure", "Start Month", "Value", [Start Month]
    ),
    SELECTCOLUMNS(
        ROW("Measure", "End Month", "Value", [End Month]),
        "Measure", "End Month", "Value", [End Month]
    ),
    SELECTCOLUMNS(
        ROW("Measure", "Duration", "Value", [Duration]),
        "Measure", "Duration", "Value", [Duration]
    )
)

3. Create a new table visual and add the Measure and Value columns of the newly created table to the visual.

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

[valima]

Hi xianjtan,

Thanks for this. I will give this a go. Looks like potentialy this could do the trick for me. So basically we can dynamically create a table with unrelated rows and display as a table. The only think I dont want is the column names at the top but I assume I should be able to hide those.

Regards

[valima]

Sorry I am a completely new to PBI visualization. Will this create a table dynamically as well? I need to show all the four values in a table not one

[dharmendars007 · Accepted solution]

Hello @valima , 

It Looks complicated but for a row reference you can create a Switch logic to achive this but it also depends on how complex your logic is..you can take some reference from the below dummy code.

MeasureValues =
SWITCH(TRUE(),
SELECTEDVALUE(MeasureNamesTable[Measure Name]) = "Worst Loss", [WorstLoss],
SELECTEDVALUE(MeasureNamesTable[Measure Name]) = "Start Month", [StartMonth],
SELECTEDVALUE(MeasureNamesTable[Measure Name]) = "End Month", [EndMonth],
SELECTEDVALUE(MeasureNamesTable[Measure Name]) = "Duration", [Duration])

If you find this helpful , please mark it as solution and Your Kudos/Likes are much appreciated!

Thank You

Dharmendar S

LinkedIN