Hide categoris values if measures value less then 3

[rufmau68]

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 

[v-kathullac · Accepted solution]

Hi @rufmau68  ,

We wanted to kindly follow up to check if the solution for the issue worked. If not, please let us know if you need further assistance. If none of the provided solutions worked, kindly share the original solution so that other community members can benefit from it in case they face a similar issue.

If our response addressed your concern, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
Chaithanya.

[v-kathullac]

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.

[rufmau68]

works only without field parameter

[v-kathullac]

Hi @rufmau68  ,

We wanted to kindly follow up to check if the solution for the issue worked. If not, please let us know if you need further assistance. If none of the provided solutions worked, kindly share the original solution so that other community members can benefit from it in case they face a similar issue.

If our response addressed your concern, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
Chaithanya.

[sjoerdvn]

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]
)

[rufmau68]

i try id, dont works 

[sjoerdvn]

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."})

[sjoerdvn]

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]
)

[v-kathullac]

Hi @rufmau68 ,

Can you share the sample pbix file to work around.

Regards,

Chaithanya.

[rufmau68]

U find it here

https://github.com/mauroruffinocsi/pbi/blob/2a36cba66a12fe694fcdbb029ec7e7975c09abd9/Segreto%20Stati...

[lbendlin]

Here's my version

Do you need the totals to work?

[rufmau68]

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 ?

[lbendlin]

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

[techies]

Hi @rufmau68 creating a calculated column might work here

Masked AgeGroup =

VAR CurrentAge = 'Deaths'[AgeGroup]

VAR Total =

    CALCULATE(

        sum(deaths[Totale_Decessi]),

        ALLEXCEPT('Deaths', 'Deaths'[Codice_Istat], 'Deaths'[AgeGroup])

    )

RETURN IF(Total <= 3, "N.I.", CurrentAge)

[rufmau68]

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)

[v-kathullac]

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.

[rufmau68]

thanks, i try it but, dont works... if U are interested I can share U a sample .pbix 

bye

mauro 

[ThxAlot]

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

[lbendlin]

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...

[rufmau68]

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