Forum Discussion

naveenskyline's avatar
naveenskyline
Regular Visitor
2 years ago
Solved

interview Question on DAX- SUMX

Hi ALL,

 

I have this question in a recent interview and want to check with you the correct DAX.

 

 Question was to write a DAX using SUMX - Assume we have multiple measures(Profit, Sales,..) and 2 dimemsions (Products,Regions). If we select profit , it should display only profit numbers for products and Regions, Similarly If we select Sales , it should display only Sales numbers for products and Regions,

 

Please help with the DAX.

 

jennratten 

Need Help
  • Shravan133's avatar
    Shravan133
    2 years ago

    -- Slicer Table
    SlicerTable =
    DATATABLE(
    "MeasureName",
    STRING,
    {
    {"Profit"},
    {"Sales"}
    }
    )

    -- Measures
    Profit = SUM('FactTable'[Profit])
    Sales = SUM('FactTable'[Sales])

    -- Dynamic Measure
    SelectedMeasure =
    VAR SelectedValue = SELECTEDVALUE('SlicerTable'[MeasureName])
    RETURN
    SWITCH(
    TRUE(),
    SelectedValue = "Profit", [Profit],
    SelectedValue = "Sales", [Sales]
    )

    -- Display Measure
    DisplayMeasure =
    SUMX(
    VALUES('DimensionTable'[Product]),
    [SelectedMeasure]
    )

1 Reply

  • Shravan133's avatar
    Shravan133
    Super User
    2 years ago

    -- Slicer Table
    SlicerTable =
    DATATABLE(
    "MeasureName",
    STRING,
    {
    {"Profit"},
    {"Sales"}
    }
    )

    -- Measures
    Profit = SUM('FactTable'[Profit])
    Sales = SUM('FactTable'[Sales])

    -- Dynamic Measure
    SelectedMeasure =
    VAR SelectedValue = SELECTEDVALUE('SlicerTable'[MeasureName])
    RETURN
    SWITCH(
    TRUE(),
    SelectedValue = "Profit", [Profit],
    SelectedValue = "Sales", [Sales]
    )

    -- Display Measure
    DisplayMeasure =
    SUMX(
    VALUES('DimensionTable'[Product]),
    [SelectedMeasure]
    )