Forum Discussion

holywasabi's avatar
holywasabi
Frequent Visitor
2 years ago
Solved

Using DAX in Visualisation

Hi all, I would like created a DAX measure to group users into multiple age ranges, it works well in table form.

But I get an error when using it in other types of visualizations (eg. pie charts / bar charts). 

Any pointers are greatly appreciated 😁 

 

= = = 

Here are my tables / data : 

Income (Grouped) =
VAR CustomerIncome = MAX( customer[ Income ] )

RETURN
CALCULATE(
    VALUES(  'Group : Income'[Income Range] ),

    FILTER(
    'Group : Income',
    CustomerIncome >=  'Group : Income'[Min] &&
    CustomerIncome <=  'Group : Income'[Max]
    )
)
 
= = = 
 
What I'd like to achieve : 
pie_chart

Thank you!

 

Tips and Tricks
  • Irwan's avatar
    Irwan
    2 years ago

    Hello holywasabi 

     

    please check if this accomodate your need.

     

    I am not sure what kind of error did you get but if it works in table form, it should work as well in other visual

     

    Apart from the error, please check these : 

    1. Created calculated column for 'Income (Grouped)'

    Income (Grouped) = MAXX(FILTER('Supporting','Income'[Income]>'Supporting'[Min]&&'Income'[Income]<'Supporting'[Max]),'Supporting'[Income Range])

     

    2. Since you want to create pie chart with 'Income Range' as separator/legend, then you need to make a measure to calculate it. Create measure in 'Supporting' Table with following DAX for calculating number of row that has exact same value in 'Income (Grouped)' and 'Income Range'.

    Count Customer =
    var _Income = SELECTEDVALUE('Supporting'[Income Range])
    var _Count = COUNTX(FILTER('Income','Income'[Income (Grouped)]=_Income),COUNTROWS('Income'))
    Return
    IF(
        _Count=BLANK(),
        0,
        _Count
    )
     

    3. Plot in pie chart, 'Income Range' as legend and the previous measure (Count Customer) as Values.

    After putting values in pie chart, in Visual Format, turn of legend and change Label Content (Detail Label -> Option).

     

    Also, in your screecshot table, you only provide two kind of data, the 10k-50k and 50k-100k. The rest of categories dont show up because the value is zero.

     

    Hope this will help you.

    Thank you.

2 Replies

  • Irwan's avatar
    Irwan
    Super User
    2 years ago

    Hello holywasabi 

     

    please check if this accomodate your need.

     

    I am not sure what kind of error did you get but if it works in table form, it should work as well in other visual

     

    Apart from the error, please check these : 

    1. Created calculated column for 'Income (Grouped)'

    Income (Grouped) = MAXX(FILTER('Supporting','Income'[Income]>'Supporting'[Min]&&'Income'[Income]<'Supporting'[Max]),'Supporting'[Income Range])

     

    2. Since you want to create pie chart with 'Income Range' as separator/legend, then you need to make a measure to calculate it. Create measure in 'Supporting' Table with following DAX for calculating number of row that has exact same value in 'Income (Grouped)' and 'Income Range'.

    Count Customer =
    var _Income = SELECTEDVALUE('Supporting'[Income Range])
    var _Count = COUNTX(FILTER('Income','Income'[Income (Grouped)]=_Income),COUNTROWS('Income'))
    Return
    IF(
        _Count=BLANK(),
        0,
        _Count
    )
     

    3. Plot in pie chart, 'Income Range' as legend and the previous measure (Count Customer) as Values.

    After putting values in pie chart, in Visual Format, turn of legend and change Label Content (Detail Label -> Option).

     

    Also, in your screecshot table, you only provide two kind of data, the 10k-50k and 50k-100k. The rest of categories dont show up because the value is zero.

     

    Hope this will help you.

    Thank you.

  • foodd's avatar
    foodd
    Community Champion
    2 years ago

    Hello holywasabi , remember to adhere to the decorum of the Community Forum when asking a question.

    Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).

    https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

    Please show the expected outcome based on the sample data you provided.

    https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523/highlight/true#M607150

    This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.