Forum Discussion

viera00's avatar
viera00
Icon for Helper II rankHelper 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
Icon for Memorable Member rankMemorable 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
Icon for Helper II rankHelper 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