Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
Hi, im new in power bi and i need to produce this result....
I have any measure aggregations that in certain scenarios ups to value of 3 (deceases, patients, fragile subjects and go on....)
in that case i have not to show that
but that
if U see, the real category values 60-69 and 70-79 are replaced with an anoymous "N.I." (means Not Identified)
And this is just a great challenge form me BUT i have another difficult problem to solve, because in my pbi report i have to activate fields parameter to offer a multi selection feature to user (in categories and also in measures). Because they have to be free to choose what to aggregate in that visual...
can anyone helps me ?
thanks at all folks!
best regards
Mauro
Hi @rufmau68 ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.
Regards,
Chaithanya.
Assumptions: there is a dimension AgeGroup, with a column Name, that includes the "N.I." value and there is a measure [# Deaths]
Based on that we can create this measure:
# Deaths (masked agegroups)=
IF (HASONEFILTER(AgeGroup[Name]),
IF(VALUES(AgeGroup[Name])="N.I.",
SUMX(All(AgeGroup[Name]), IF([# Deaths] <=3, [# Deaths])),
[# Deaths]
),
[# Deaths]
)
i try id, dont works
You will need the "AgeGroup" as a seperate (dimension) table. For a quick fix you can add a Computed table (see example below) and then create a relationship with that fact table, and use the new dimension table column in your visual.
AgeGroups = UNION(SUMMARIZE(Table13,Table13[Fascia_Eta]), {"N.I."})
I created a litte test and I needed to add something to it, to make it work.
# Deaths (masked agegroups) =
IF (HASONEFILTER(AgeGroup[Fascia_Eta]),
IF(VALUES(AgeGroup[Fascia_Eta])="N.I.",
SUMX(All(AgeGroup[Fascia_Eta]), IF([Decessi] <=3, [Decessi])),
IF([Decessi]>3,[Decessi])
),
[Decessi]
)
yes but U have used a summarizedcolumn() dax function to create a temporary tables to previous evaluate aggregated values... that is a good idea BUT if U have a scenarios where user choose (from a field parameter) what category aggregate... one time anno & fascia_Eta and another time only fascia_Eta, in this 2 cases i have to use 2 different summarizedcolumn() functions.... (alternatively).... there is no any solution where i can define just one (dynamic) summarizedcolum() and use always the same ?
If you check the PBIX you will see that I also added a calculated table.
I don't think a dynamic solution is possible at the moment.
If this is important to you please consider voting for an existing idea or raising a new one at https://ideas.fabric.microsoft.com
Hi @rufmau68 creating a calculated column might work here
Hi @techies , this solution works but its a static solution.
in allexcept() i dont know what column to use because my user select aggregations columns from a field parameter filter , in my sample i use (by exemple) 'Deaths'[Codice_Istat], 'Deaths'[AgeGroup]
but my user can select any other column showed in the fact tables... so i dont know how to configure dinamically allexcept arguments....
in a below post a data semple with codice_istat, age_group columns but also year column (Anno in italian).
if my user from field parameter filter select anno and age group my allexcept have to manage anno and age group
if my user select anno and codice_istat, my allexcept have to manage anno and codice_istat
and so on (in the semple i just use 3 columns but, in my real fact table i have many more columns then 3)
Hi @rufmau68 ,
Thank you for reaching out to Microsoft Fabric Community Forum.
You want to mask the AgeGroup field if the total number of deaths is small (e.g., <=3), but the aggregation should respect the columns selected by the user in the report (via a Field Parameter)
Can you try with the below dax it may solve your issue.
Masked AgeGroup =
VAR CurrentAge = 'Deaths'[AgeGroup]
VAR IsAnnoSelected = ISINSCOPE('Deaths'[Anno])
VAR IsCodiceIstatSelected = ISINSCOPE('Deaths'[Codice_Istat])
VAR IsAgeGroupSelected = ISINSCOPE('Deaths'[AgeGroup])
VAR Total =
CALCULATE(
SUM('Deaths'[Totale_Decessi]),
REMOVEFILTERS('Deaths'),
KEEPFILTERS(
{
IF(IsAnnoSelected, VALUES('Deaths'[Anno])),
IF(IsCodiceIstatSelected, VALUES('Deaths'[Codice_Istat])),
IF(IsAgeGroupSelected, VALUES('Deaths'[AgeGroup]))
}
)
)
RETURN IF(Total <= 3, "N.I.", CurrentAge)
Regards,
Chaithanya.
thanks, i try it but, dont works... if U are interested I can share U a sample .pbix
bye
mauro
Congrats! Even though you're a rookie in BPI, this question beats quite many so-called superusers. Now, all you need to do,
1. paste some well-prepared mockup dataset (I don't bother to manully prepare it)
2. wait patiently for nice solutions, if any
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...
Thans, here a csv data semple...
Anno | Causa_Morte | Codice_Istat | Fascia_Eta | N_Decessi | |
4045 | Causa 1 | 1187 | 0-5 | 4 | |
6067 | Causa 1 | 1187 | 60-69 | 6 | |
6067 | Causa 1 | 1272 | 0-5 | 3 | |
4045 | Causa 1 | 1272 | 60-69 | 2 | |
4045 | Causa 2 | 1187 | 0-5 | 2 | |
2023 | Causa 2 | 1187 | 60-69 | 1 | |
6068 | Causa 2 | 1272 | 0-5 | 5 | |
2022 | Causa 2 | 1272 | 60-69 | 1 | |
2023 | Causa 2 | 1272 | 70-79 | 1 | |
2023 | Causa 3 | 1187 | 70-79 | 1 |
User | Count |
---|---|
15 | |
10 | |
9 | |
9 | |
8 |