Forum Discussion
Creating counted labels since table to feed Clustered Column Chart
File sample is here:
https:/1drv.ms/x/c/83ababf8fc4e8a05/EXD9milO16RJujUlbV_alPQBiHSgdeRfB-7R1d3wpfF99Q
I've been working on next project to create labeling at the row level (CustomerID and Fiscal Year) however after trying several ways labeling fails showing labels that do not pass data quality verification. That is way I am asking for your help here guys.
Original semantic model dataset (table) shows: 42 Dimensions and 5 Measures (47 total columns).
This table has to be used to create a Clustered Column Chart which will use between 10 to 15 filters (Dimension Columns which one of them is date type).
The sample excel table provided in above OneDrive link (Data worksheet) only shows 4 columns (3 dimensions and 1 measure) to ilustrate.
During Labeling 5 labels are calculated ("Year", "Repeated", "Lost", "New Gained", "Re-gained") however only 4 labels are used on Clustered Column Chart ("Year", "Lost", "New Gained", "Re-gained"):
Labeling Criteria is as follow:
Year:
FY 2018: FY 2018 CustomerID Quantity under "Repeated" + FY 2018 CustomerID Quantity under "Lost"
FY 2019: FY 2019 CustomerID Quantity under "Repeated" + FY 2019 CustomerID Quantity under "Lost"
FY 2020: FY 2019 CustomerID Quantity under "Repeated" + FY 2019 CustomerID Quantity under "Lost"
etc. etc.
FY 2024: FY 2024 CustomerID Quantity under "Repeated" + FY 2024 CustomerID Quantity under "Lost"
Repeated:
FY 2018: IF Sum(Revenue FY 2018) <> 0 && Sum(Revenue FY 2019) <> 0 THEN FY 2018 CustomerID is "Repeated"
FY 2019: IF Sum(Revenue FY 2019) <> 0 && Sum(Revenue FY 2020) <> 0 THEN FY 2019 CustomerID is "Repeated"
FY 2020: IF Sum(Revenue FY 2020) <> 0 && Sum(Revenue FY 2021) <> 0 THEN FY 2020 CustomerID is "Repeated"
etc. etc.
FY 2024: IF Sum(Revenue FY 2024) <> 0 && Sum(Revenue FY 2025) <> 0 THEN FY 2024 CustomerID is "Repeated"
Lost:
FY 2018: IF Sum(Revenue FY 2018) <> 0 && Sum(Revenue FY 2019) = 0 THEN FY 2018 CustomerID is "Lost"
FY 2019: IF Sum(Revenue FY 2019) <> 0 && Sum(Revenue FY 2020) = 0 THEN FY 2019 CustomerID is "Lost"
FY 2020: IF Sum(Revenue FY 2020) <> 0 && Sum(Revenue FY 2021) = 0 THEN FY 2020 CustomerID is "Lost"
etc. etc.
FY 2024: IF Sum(Revenue FY 2024) <> 0 && Sum(Revenue FY 2025) = 0 THEN FY 2024 CustomerID is "Lost"
New Gained:
FY 2018: IF Sum(Revenue FY 2018) = 0 && Sum(Revenue FY 2019) <> 0 THEN FY 2018 CustomerID is "New Gained"
FY 2019: IF Sum(Revenue FY 2018) + Sum(Revenue FY 2019) = 0 && Sum(Revenue FY 2020)<>0 THEN FY 2019 CustomerID is "New Gained"
FY 2020: IF Sum(Revenue FY 2018) + Sum(Revenue FY 2019) + Sum(Revenue FY 2020) = 0 && Sum(Revenue FY 2021)<>0 THEN FY 2020 CustomerID is "New Gained"
etc. etc.
FY 2024: IF Sum(Revenue FY 2018) + Sum(Revenue FY 2019) + Sum(Revenue FY 2020) + Sum(Revenue FY 2021) + Sum(Revenue FY 2022) + Sum(Revenue FY 2023) + Sum(Revenue FY 2024) = 0 && Sum(Revenue FY 2025)<>0 THEN FY 2024 CustomerID is "New Gained"
Re-gained
FY 2018: FY 2018 CustomerID is 0
FY 2019: IF SUM(Revenue FY 2018)<>0 && SUM(Revenue FY 2019)=0 && SUM(Revenue FY 2020) <> 0 THEN FY 2019 CustomerID is "Re-gained"
FY 2020: IF SUM(Revenue FY 2018) + SUM(Revenue FY 2019) <>0 && SUM(Revenue FY 2020) = 0 && SUM(Revenue FY 2021) <> 0 THEN FY 2020 CustomerID is "Re-gained"
etc. etc.
FY 2024:IF SUM(Revenue FY 2018) + SUM(Revenue FY 2019) + SUM(Revenue FY 2020) + SUM(Revenue FY 2021) + SUM(Revenue FY 2022) + SUM(Revenue FY 2023) <>0 && SUM(Revenue FY 2024) = 0 && SUM(Revenue FY 2025) <> 0 THEN FY 2024 CustomerID is "Re-gained"
6 Replies
- Deku
Super User
What is the question?
Thank you Deku for your reply.
Could you please advise how DAX measures (Year, Re-gained, New Gained, Lost) shold be defined in order to be used in the Clustered Column Chart allowing incorporate other table dimensions as fiilters?.
Hi PowerTab ,
Thank you for reaching out to the Microsoft fabric community forum.
To resolve your issue, create DAX measures that check revenue per CustomerID across fiscal years to classify them as Repeated, Lost, New Gained, and Re-gained. Use numeric fiscal year values for easy comparisons. For each category, define measures like:
- Repeated: Revenue in current year > 0 AND next year > 0
- Lost: Revenue in current year > 0 AND next year = 0
- New Gained: No revenue before current year AND revenue in current year > 0
- Re-gained: Revenue before current year > 0, zero revenue current year, revenue next year > 0
Then aggregate these 0/1 measures over customers to count for each fiscal year, so your clustered column chart updates correctly with all filters applied.
This approach ensures accurate labeling and dynamic filtering within your complex model.
I hope this will resolve your issue, if you need any further assistance, feel free to reach out.
If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.
Thankyou.
Hi PowerTab ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.
Hi PowerTab ,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
Hi @PowerTab ,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
