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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
QuangMC
Frequent Visitor

Can you do it with measures - Compare 2 results by 2 segments on 1 table

Hello,

 

I would like to know if it is possible to compare 2 values based on 2 different segments, but both segments are based on the same table.

 

Let me explain.

I have a simple model, with 2 tables in a 1 to * relationship (dim to fact) : 

- Dim1, with information on vehicules (VehiculeNumber, VehiculeSeries)

- Faits1, with vehicule performances (VehiculeNumber, Litres, Km...)

QuangMC_1-1689686989375.png

 

Let's say, I would like to be able to compare performances between specific Series.

I want to be able to select a "reference Series" and a "compare with Series", so I add 2 segments, with the same column, but I deactivate interactions between them so I can view the entire series.

 

I need to add a measure that would calculate the sum of Litres from one segment - the sum of Litres from the other segment.

I can then add this measure to a single card visual for example.

 

To put it "simply" :

- Value1 = Sum of Litres from Segment1

- Value2 = Sum of Litres from Segment2

- Variation = Value1 - Value2

 

Problem is, since Segment1 and Segment2 are based on the same column (Dim1[Series]), any way I tried was unsuccessful. 

In image : 

QuangMC_0-1689686932369.png

 

 

I know of a way to get what i want, by just adding a second Dim table similar to the first one, and then just using Series from both Dim tables into different segments. But this method seems terrible when your model gets bigger, as I would need to duplicate certain tables for this sole purpose.

 

I thought of using ALL, ALLEXCEPT, or ALLSELECTED, but it doesn't seem to work. I have also tried adding a second, identical "Series" column in the Dim1 table, and changed the segments accordingly, but still no work around. 

 

So, does anyone know if it is possible to do this with DAX and measures ? Or do I really need to have different tables ?

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

You would need to have 2 tables. You could create the second one like

Series 2 = ALLNOBLANKROW( DIM_1[Series] )

Don't create a relationship from this table to any others, just use it in your slicer and then you can create a measure like

Variation =
VAR Val1 =
    SUM ( Faits_1[Litres] )
VAR Val2 =
    CALCULATE (
        SUM ( Faits_1[Litres] ),
        TREATAS ( VALUES ( Series2[Series] ), DIM_1[Series] )
    )
RETURN
    Val1 - Val2

View solution in original post

2 REPLIES 2
johnt75
Super User
Super User

You would need to have 2 tables. You could create the second one like

Series 2 = ALLNOBLANKROW( DIM_1[Series] )

Don't create a relationship from this table to any others, just use it in your slicer and then you can create a measure like

Variation =
VAR Val1 =
    SUM ( Faits_1[Litres] )
VAR Val2 =
    CALCULATE (
        SUM ( Faits_1[Litres] ),
        TREATAS ( VALUES ( Series2[Series] ), DIM_1[Series] )
    )
RETURN
    Val1 - Val2

Okay, thank you, this works great. Too bad we can't do this we just one table, but still cool to be able to have this option.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors