DISTINCOUNT IF certain condition met

[ckhoo2007]

Hi All,

I have a table which has Date, Client and Sales columns. The Date column has relationship with "Date" table and I have a date slicer to select certain date range.

I have created 3 measures to calculate the "T-30days sales", "T-60 days sales", and "Diff 30-60" which is basically (T-30) - (T-60).

May I know what is the right DAX formula to calculate the disticnt client who has sales reduced based on Diff 30-60? I used formula like below but it returns an error:

30days_Degraded = CALCULATE(DISTINCTCOUNT(Sales[Client]), (Sales[Diff30-60] < 0) , FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))

Error: A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

Thanks.

Regards,

Choo

[v-janeyg-msft · Accepted solution]

Hello, @ckhoo2007 

I create two measures to sum total in pie chart. I also modified the total to make it display correctly. You can check the below sample file.

Like this:

Measure1 = 
SUMX (
    ADDCOLUMNS (
        ADDCOLUMNS (
            SUMMARIZE (
                ALLSELECTED(  Sales ),
                Sales[Client],
                "t30",
                    CALCULATE (
                        DIVIDE (
                            SUMX ( 'Sales', 'Sales'[Sales] * 'Sales'[Transactions] ),
                            SUM ( 'Sales'[Transactions] )
                        ),
                        DATESINPERIOD ( Sales[Date], MAX ( Sales[Date] ), -30, DAY )
                    ),
                "t60",
                    CALCULATE (
                        DIVIDE (
                            SUMX ( 'Sales', 'Sales'[Sales] * 'Sales'[Transactions] ),
                            SUM ( 'Sales'[Transactions] )
                        ),
                        DATESINPERIOD ( Sales[Date], MAX ( Sales[Date] ) - 30, -30, DAY )
                    )
            ),
            "t30-t60", [t30] - [t60]
        ),
        "bb", IF ( [t30-t60] > 0, 1 )
    ),
    [bb]
)

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,

Community Support Team _ Janey

[ckhoo2007]

Thanks Janey.

[v-janeyg-msft]

Hi, @ckhoo2007 

There is no context in the pie chart, so there may be problems with the value of the measure.

There is no problem with the table you shared, because these columns are all measures. Can you share a sample fake data sample? So we can help you soon.

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,

Community Support Team _ Janey

[ckhoo2007]

Thanks Janey and Nidhi.

I have the sample database in excel and sample pbix, stupid question, how do I attached here?

Regards,

Choo

[v-janeyg-msft]

Hello, @ckhoo2007 

Please upload sample files to onedrive for business, and then send the anonymous link here.

Best Regards,

Community Support Team _ Janey

[ckhoo2007]

https://1drv.ms/u/s!Al0gBcgeYcO6bpYur2h0MnIAqrE?e=9By5m0

Thanks.

[NidhiBhusari]

@ckhoo2007 can you share your .pbix file or sample dataset?

[NidhiBhusari]

@ckhoo2007 can you try implementing this DAX and use date in a slicer.

T = IF([sales total]< 0,DISTINCTCOUNT(Sheet1[Client]))

Here [Sales Total] is Sales[Diff30-60].

[ckhoo2007]

Thanks very much Nidhi, yes it works I mean I can calculate the client however it seems like they all returned as Improved, but when I populate the Diff30-60 I can clearly see positive and negative numbers, any reason why?

Client	T-30Sales	T-60Sales	Diff30-60
2D	25.80517	26.16336	-0.3582
A3	12.25654	12.06679	0.18975
G2	13.00134	12.96319	0.03815
A8	25.75107	25.88478	-0.1337
B2	11.91763	12.65704	-0.73941
C1	10.24266	9.792654	0.45
C2	14.46511	13.35042	1.11469
D2	15.89932	15.62133	0.27799
D3	11.68738	11.94896	-0.26159
D4	12.71874	12.56913	0.14961
G4	11.29752	11.08111	0.21641
G1	8.983207	8.910448	0.07276
J1	15.6621	15.1474	0.5147
L9	33.1128	34.75687	-1.64407
M2	30.38839	30.25823	0.13017
M8	14.73733	15.79288	-1.05555
N3	12.33259	12.29678	0.03582
N2	21.52088	23.32267	-1.80179

I used the following formula:

Improved = IF([Diff30-60]> 0,DISTINCTCOUNT(Sheet1[Client]))

Degraded = IF([Diff30-60]< 0,DISTINCTCOUNT(Sheet1[Client]))

Thanks again.

Regards,

Choo

[ckhoo2007]

Sorry some correction, when I try to plot the improved or degraded in a chart it all shows improved.....

[ckhoo2007]

Thanks Nidhi, let me work out the sample data set and share later.

[NidhiBhusari]

Hey @ckhoo2007 I tried implementing this scenario that "when I try to plot the improved or degraded in a chart it all shows improved....." using pie chart and as per my analysis I think if your measure generates negative values and are used in pie chart then it won't appear in visual.

[ckhoo2007]

but that's why I used Distinctcount which should count only those improved or degraded hence botn are positive numbers.....