DAX Code

[Singh_Yoshi]

I have a table Called Table1 and columns as, Model code, Items, MSRP, Cost.
model Code has following values, FJ11, FJ16,FJ7,FJ8,FJ15,FJ12. Now the requirement is to compare one model with another model of its Items, MSRP and Costs. I have already created few measures using joins to compare each model with another model.(Measure: FJ11vsFJ16, FJ16 vs FJ7, FJ11vsFJ8, FJ7vs FJ8 ect)

Now I just want to create two slicers of Model Code. And I want to select one model in Slicer 1 and another model in slicer 2 and get result of those created measures items and its cost, MSRP, Difference of MSRP.

Kindly provide me code with example file.

[Ray_Minds · Accepted solution]

Hi @Singh_Yoshi 

If you want to compare models using two slicer then you need duplicate your table
and then use below DAX measure

Step 1 :Create Duplicate Table(Ex. Duplicate Table1 and name it Table2)
Step 2 :Add two slicers based on Table1[Model Code] and another based on Table2[Model Code]
Step 3 :Creating DAX Measures for Selected Models

--Measure
In Table1
SelectedModel1 = SELECTEDVALUE('Table1'[Model Code])

In Table2
SelectedModel2 = SELECTEDVALUE('Table2'[Model Code])

Step 4 : Comparison Measures
MSRPComparison =
VAR Model1 = [SelectedModel1]
VAR Model2 = [SelectedModel2]
RETURN
IF (
    NOT (ISBLANK(Model1)) && NOT (ISBLANK(Model2)),
    CALCULATE (
        SUM('Table1'[MSRP]),
        'Table1'[Model Code] = Model1
    ) - 
    CALCULATE (
        SUM('Table2'[MSRP]),
        'Table2'[Model Code] = Model2
    ),
    BLANK()
)

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

[grazitti_sapna]

Hi @Singh_Yoshi 

To implement this solution, follow these steps:

1. Create a SlicerTable to enable the selection of Mode Code.
2. Use the following formula to create the necessary measure:

   SelectedModel1 = SELECTEDVALUE(slicer1[Model code], "None")
   SelectedModel2 = SELECTEDVALUE(slicer2[Mode code], "None")
   
   

   MSRP_Difference =
   VAR Model1 = [SelectedModel1]
   VAR Model2 = [SelectedModel2]
   RETURN
   CALCULATE(SUM('Table'[MSRP]), 'Table'[Model code] = Model1)
   - CALCULATE(SUM('Table'[MSRP]), 'Table'[Model code] = Model2)
   
   

   I hope this solution is helpful! If it works for you, please consider accepting it as the solution, and a kudos is always appreciated.

   

[Anonymous]

Hi @Singh_Yoshi ,

You should create slicerTable to select Mode Code. And then use below formula to create measure:

SelectedModel1 = SELECTEDVALUE(slicer1[Model code],"None")

SelectedModel2 = SELECTEDVALUE(slicer2[Mode code],"None")

MSRP_Difference =
VAR Model1 = [SelectedModel1]
VAR Model2 = [SelectedModel2]
RETURN
    CALCULATE ( SUM ( 'Table'[MSRP] ), 'Table'[Model code] = Model1 )
        - CALCULATE ( SUM ( 'Table'[MSRP] ), 'Table'[Model code] = Model2 )

Best Regards,
Adamk Kong

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

[MFelix]

Hi @Singh_Yoshi ,

This depends on how you have your model, but if you want to have two slicers then you need to have two different tables with the products so that you can use it on the slicers then you need to do a measure similar to this:

Product Comparision =
var FirstProduct = SELECTEDVALUE(Table1[Product])
var SecondProduct = SELECTEDVALUE(Table2[Product])

Return
CALCULATE(SUM(Table[Value]), Table[Product] = FirstProduct) - CALCULATE(SUM(Table[Value]), Table[Product] = SecondProduct)

This is just a simple measure that does the difference between both values, be aware that the two tables need to be disconnected from the initial table, and if you select more than one product on the slicer this calculation will return an error.

[SamWiseOwl]

Hi @Singh_Yoshi 

You want to create two new tables that contain just the model code:
Model Table 1 

Model 1

FJ11, FJ16,FJ7,FJ8,FJ15,FJ12

Model Table 2 

Model 2

FJ11, FJ16,FJ7,FJ8,FJ15,FJ12

These tables can be created using Enter Data or in the Query Editor duplicate your original table twice and delete the extra columns. When loaded DO NOT join these into your model.

Now put these columns in two seperate slicers set to single select, this will let your user choose the two to compare.

Now you can create measure or edit your existing measures to take this into account using the SELECTEDVALUE function. This returns the only item in the filtered column.

For example to return the MSRP for item 1:
Calculate(

               SelectedValue('Table 1'[MSRP]) --Use SUM if you are adding, SV to return a single value

               ,'Table 1'[Model Code] = SelectedValue('Mode Table 1'[Model 1])

)

--This replaces the existing Model Code with the one selected in slicer 1

Now repeat for the second slicer.