Provide a criteria for the result values based on the slicer selection

[emohammad]

Hi,
I am getting the wrong result when I am selecting the slicer selections. 
I trying to create the count of emails as per the provided criteria by country. The values and criteria should change as per the slicer selections.

I could able to see the correct values when do not select any slicer filters but the values are not correct when I select the slicers.

Please help me out here.

The below snapshot is the source data.

Below are the Measures I created:

1.

Income_measure =

CALCULATE(SUM(Sheet1[Income]),

ALLEXCEPT(Sheet1,

Sheet1[Continent],

Sheet1[Country],

Sheet1[Email]),

ALLSELECTED(Sheet1[Source], Sheet1[Type]))

2.

Unique_emails = DISTINCTCOUNT(Sheet1[Email])

Created column for criteria:

Criteria =

IF([Income_measure]=0,"No income",IF([Income_measure]>=50,"High income","Intremediate income"))

Below snapshot is the wrong result I am getting based on my selection criteria

Your help in this regard is most appreciated.

Thanks,
Eliyaz.

[selimovd]

Hey @emohammad ,

as far as I've seen all the slicers and the data are accessing the same table. Is that right? 

In that case I think it might be the auto exist feature that is causing wrong results:

Understanding DAX Auto-Exist - SQLBI

Change your data model to a star schema, then the results should be calculated correctly.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

[emohammad]

Hi @selimovd,
Thanks for your reply. Yes, the data is accessing for the same table. Can you please provide bit more details for the solution? 
I tried using GROUPBY as

sum_income_groupby =

GROUPBY(Sheet1,Sheet1[Country],Sheet1[Email],"sum_income",

SUMX(CURRENTGROUP(),Sheet1[Income])).
Again, values are not changing as per slicers selections.
Thanks,

Eliyaz.

[selimovd]

Hey @emohammad ,

you cannot solve that with a measure. You have to change your data model.

All columns except for the income have to be put into separate dimensional tables. Then you use the columns from the dimensional tables to slice and for your measures. Then the result will be correct.

Here are some basics about dimensional tables:

Basics of Modeling in Power BI: What is a Dimension Table and Why Say No to a Single Big Table - RAD...

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

[ryan_mayu]

@emohammad 

what about create a creteria column in the table

COLUMN=
VAR Income_measure =
CALCULATE(SUM(Sheet1[Income]),
ALLEXCEPT(Sheet1,
Sheet1[Continent],
Sheet1[Country],
Sheet1[Email]),
ALLSELECTED(Sheet1[Source], Sheet1[Type]))
return IF([Income_measure]=0,"No income",IF([Income_measure]>=50,"High income","Intremediate income"))

[AlexisOlson]

This looks like what OP did. The problem is that a calculated column cannot be responsive to slicers but you can't use a measure as the legend in the bar chart.

This needs an independent parameter table to use for the legend.

[emohammad]

Thanks for your reply,
Can you please guide me on what can be done here with regards to the independent parameter table?
I also tried creating three measures for each criterion to color the values as shown below.

1. No Income =

IF([Income_measure]=0,DISTINCTCOUNT(Sheet1[Email]),0)

2. Intermediate Income =

IF([Income_measure]>=1 && [Income_measure]<=49,DISTINCTCOUNT(Sheet1[Email]),0)
3.High Income =

IF([Income_measure]>=50,DISTINCTCOUNT(Sheet1[Email]),0)
Again, incorrect values.

Thanks,
Eliyaz.

[AlexisOlson]

That looks like it lines up with the Income_Measure column. What values are you expecting instead?