Forum Discussion

viera00's avatar
viera00
Helper II
9 years ago
Solved

Dynamic Table in DAX for Bin

Hello everyone.   I've an interesting requirement that has become a challenge right now. It sounds simple and the data is only contained in three tables, but the I've been strugling to implement th...
Need Help
Tips and Tricks
  • SqlJason's avatar
    SqlJason
    9 years ago

    Not sure I understand all the requirements, but let me give it a try.

    1) Create a calculated table (should be a disconnected table) which will have an extra row for Other. This is the column which will be used in the chart.

    ProdSubCat_List = UNION(VALUES(ProductSubcategory[Product Subcategory Name]), 
    ROW("SubCategoryName", "Other"))

    2) Create a measure

    NewSalesMeasure = 
VAR SelectedSales =
    CALCULATE (
        [Sales Amount],
        INTERSECT (
            VALUES ( ProductSubcategory[Product Subcategory Name] ),
            VALUES ( ProdSubCat_List[Product Subcategory Name] )
        )
    )
VAR AllSelectedSales =
    CALCULATE (
        [Sales Amount],
        INTERSECT (
            VALUES ( ProductSubcategory[Product Subcategory Name] ),
            ALL ( ProdSubCat_List[Product Subcategory Name] )
        )
    )
VAR AllSales = CALCULATE (
                [Sales Amount],
                ALL ( 'ProductSubcategory'[Product Subcategory Name] )
            )
RETURN
    IF (
        HASONEVALUE ( ProdSubCat_List[Product Subcategory Name] ),
        SWITCH (
            VALUES ( ProdSubCat_List[Product Subcategory Name] ),
            "Other", AllSales - AllSelectedSales,
            SelectedSales
        ),
        AllSales
    )

     3) Create a column chart with ProdSubCat_List[Product Subcategory Name] on axis and NewSalesMeasure on values. Put a slicer which has ProductSubcategory[Product Subcategory Name] 

     This should work

     

    Update - 3/1/2017 I blogged about this here - http://sqljason.com/2017/03/dynamic-grouping-in-power-bi-using-dax.html

    I have also optimized the formula in the blog, in case anyone is interested

SqlJason's avatar
SqlJason
Memorable Member
9 years ago

Not sure I understand all the requirements, but let me give it a try.

1) Create a calculated table (should be a disconnected table) which will have an extra row for Other. This is the column which will be used in the chart.

ProdSubCat_List = UNION(VALUES(ProductSubcategory[Product Subcategory Name]), 
ROW("SubCategoryName", "Other"))

2) Create a measure

NewSalesMeasure = 
VAR SelectedSales =
    CALCULATE (
        [Sales Amount],
        INTERSECT (
            VALUES ( ProductSubcategory[Product Subcategory Name] ),
            VALUES ( ProdSubCat_List[Product Subcategory Name] )
        )
    )
VAR AllSelectedSales =
    CALCULATE (
        [Sales Amount],
        INTERSECT (
            VALUES ( ProductSubcategory[Product Subcategory Name] ),
            ALL ( ProdSubCat_List[Product Subcategory Name] )
        )
    )
VAR AllSales = CALCULATE (
                [Sales Amount],
                ALL ( 'ProductSubcategory'[Product Subcategory Name] )
            )
RETURN
    IF (
        HASONEVALUE ( ProdSubCat_List[Product Subcategory Name] ),
        SWITCH (
            VALUES ( ProdSubCat_List[Product Subcategory Name] ),
            "Other", AllSales - AllSelectedSales,
            SelectedSales
        ),
        AllSales
    )

 3) Create a column chart with ProdSubCat_List[Product Subcategory Name] on axis and NewSalesMeasure on values. Put a slicer which has ProductSubcategory[Product Subcategory Name] 

 This should work

 

Update - 3/1/2017 I blogged about this here - http://sqljason.com/2017/03/dynamic-grouping-in-power-bi-using-dax.html

I have also optimized the formula in the blog, in case anyone is interested

  • viera00's avatar
    viera00
    Helper II
    9 years ago

    Dear SqlJason ,

     

    Thank you very much for this solution. This is really what I was looking for. 

     

    There are several posts online looking for this solution. I will try to link it to this post and credit you for the code.

     

    Initially, when I pasted the code, as was not able to make it work. The "Other" field was returnin "0", and that was because the variables "AllSelectedSales" and "AllSales" where returning the same. Only when I replaced the ALL filter in the AllSelectedSales variable , for a ALLSELECTED, is that it started to work. I do not indentify exactly why, but it is working. So the change was:

     

    VAR AllSelectedSales =
    CALCULATE (
       [TotalSales],
        INTERSECT (
            VALUES(DimProductSubcategory[EnglishProductSubcategoryName]) ,
            ALLSELECTED(ProdSubCat_List[EnglishProductSubcategoryName])
        )
    )

    Any clue on why this happened ?

     

    Thanks again, and hope you continue sharing your knowledge.

     

    Have a nice day,

     

    Regards,

     

    GV

    • SqlJason's avatar
      SqlJason
      Memorable Member
      9 years ago

      You are welcome :), glad that it was what you wanted.

       

      As for the change, I can't say for sure why that would be the case unless I see your model and what you are placing on the visuals. But if this new table (which is ProdSubCat_List) is not used anywhere else in the visuals or filters, they should return the same for ALL and ALLSELECTED. The 

      VALUES(DimProductSubcategory[EnglishProductSubcategoryName])

      is what drives the selection, so it shouldn't matter ideally. 

      • Sean's avatar
        Sean
        Community Champion
        9 years ago

        Yes I couldn't get it to give me different results with ALL vs ALLSELECTED

         

        But really useful Measure! :smileyhappy:

         

        Already incorporated in my reports!

         

        Phil_Seamark, Anonymous, Anonymous, Vvelarde

  • MMBT's avatar
    MMBT
    Regular Visitor
    3 years ago

    Hi SqlJason,

     

    thanks for the awesome reply, I can use that right now! I was wondering how to use this, if I need to have a stacked Slicer with Category and Subcategory? Obvioulsy add both of these columns to the calculated table but I struggle to change the measure to work now 😄


    Thanks, really appreciate it!