topic Re: Card values depends on slicer selection in DAX Commands and Tips

Card values depends on slicer selection

DineshArivu — Tue, 12 Aug 2025 14:51:06 GMT

Hi Experts,

I am using 2 slicers with min & max values and few cards to display the counts based on the selected slicer values as below :

below the query which is executed from snowflake data source and the expected result is 490 , but i am getting 447 with all the filters applied in this query (using slicers in power bi report)

select * from datalake.dm_xxx.vw_opportunity_quote_source where sales_involvment='Sales Opportunities' and opportunity_stage='Closed Won' and closed_date between '2025-01-01'and'2025-06-30' and (amount_in_usd between '0' and '50000' or churn_in_usd between '-50000' and '0');

here amount_in_usd and churn_in_usd values are sample values , but user can select any values here.
this conditions should use in all the above cards in the first pic.

What i tried and no luck :

CALCULATE(DISTINCTCOUNT('Opportunity Quote Source'[SFDC_ENSONO_OPPORTUNITY]),

KEEPFILTERS(

'Opportunity Quote Source'[AMOUNT_IN_USD] IN VALUES('Opportunity Quote Source'[AMOUNT_IN_USD]) ||

'Opportunity Quote Source'[CHURN_IN_USD] IN VALUES('Opportunity Quote Source'[CHURN_IN_USD])

)

Please help to achieve this result

Thanks

Re: Card values depends on slicer selection

Greg_Deckler — Tue, 12 Aug 2025 15:07:36 GMT

DineshArivu Nothing in your SQL statement is doing anything regarding DISTINCT. Try replacing your DISTINCTCOUNT with COUNTROWS and see if you get the 490.

Re: Card values depends on slicer selection

DineshArivu — Tue, 12 Aug 2025 15:26:33 GMT

Greg_Deckler No Luck . Still it is showing 447 ..

Total Opportunity ID = CALCULATE(COUNTROWS('Opportunity Quote Source'),

KEEPFILTERS(

'Opportunity Quote Source'[AMOUNT_IN_USD] IN VALUES('Opportunity Quote Source'[AMOUNT_IN_USD]) ||

'Opportunity Quote Source'[CHURN_IN_USD] IN VALUES('Opportunity Quote Source'[CHURN_IN_USD])

)

)

We have to calculate a specific columns for this : Oppotunity ID

Re: Card values depends on slicer selection

Selva-Salimi — Tue, 12 Aug 2025 15:37:56 GMT

Hi DineshArivu

Did you have any Date table in your model which might have any relation with your main table?

Re: Card values depends on slicer selection

DineshArivu — Tue, 12 Aug 2025 15:52:30 GMT

Selva-Salimi No , here we are using a single table which has all the date related columns. here the issue is depends on the amount and churn slicers only .. without this slicer the values matches with snowflake but when we filter any values in the slicers then we got wrong counts in all cards.

Re: Card values depends on slicer selection

Greg_Deckler — Tue, 12 Aug 2025 16:01:37 GMT

DineshArivu Probably something wonky with CALCULATE yet again, try a No CALCULATE approach:

No CALCULATE Measure = VAR __Table = FILTER( 'Opportunity Quote Source', [AMOUNT_IN_USD] IN DISTINCT('Opportunity Quote Source'[AMOUNT_IN_USD]) || [CHURN_IN_USD] IN DISTINCT( 'Opportunity Quote Source'[CHURN_IN_USD] ) VAR __Result = COUNTROWS( __Table ) RETURN __Result

Honestly, I'm not sure why just COUNTROWS( 'Opportunity Quote Source' ) wouldn't work.

Re: Card values depends on slicer selection

Selva-Salimi — Tue, 12 Aug 2025 16:18:25 GMT

It’s possible that the difference in the count of opportunities between Snowflake and Power BI is caused by blank or NULL values.
I recommend creating a table visual in Power BI showing opportunity ID, churn, amount, then export that table to Excel or ...

Do the same in Snowflake export the query results to Excel or ....
Once you have both datasets, Excel’s lookup functions to compare the two and identify where the differences occur.

If this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

Re: Card values depends on slicer selection

DineshArivu — Tue, 12 Aug 2025 16:26:07 GMT

Greg_Deckler No luck, Still the count is not good 😞
here in the DAX just filtering and not calculating any column counts.
Column name : Opportunity ID

Re: Card values depends on slicer selection

DineshArivu — Tue, 12 Aug 2025 16:48:24 GMT

Selva-Salimi Greg_Deckler

Yes actually i found the issue that the following condition properly applied in snowflake - (amount_in_usd between '0' and '50000' OR churn_in_usd between '-50000' and '0') - OR logic.

But in Power BI , it is applied AND logic , here what i stuck .. attached the comparison below .
example : 3M company presence 4 rows in SF data (OR logic) but in DAX it is 3 rows (AND logic)
Snowflake data :

ACCOUNT_NAME

SFDC_ENSONO_OPPORTUNITY

KANTATA_OPPORUNITY

EIM_OPPORTUNITY

SP_OPPORTUNITY

AGREEMENT_SIGNATURE_DATE

OPPORTUNITY_NAME

OPPORTUNITY_STAGE

CLOSED_DATE

CHURN_IN_USD

AMOUNT_IN_USD

TYPE_OF_ORDER

SALES_INVOLVMENT

QUOTE_SOURCE

3M Company

202412-64440

3M CP3000 study for z16 to migrated to DG

Closed Won

2/12/2025

New

Sales Opportunities

No Quote

3M Company

202412-64446

3M IBMi Hong Kong egress project and China project overage billing

Closed Won

2/6/2025

587.25

New

Sales Opportunities

No Quote

3M Company

202503-65670

3M Mainframe Application Latency Risk Assessment

Closed Won

4/17/2025

6250

New

Sales Opportunities

No Quote

3M Company

202506-66602

006Qs00000RYqmRIAT

3M contract extension with commitment to digital services

Closed Won

6/26/2025

108333.33

New

Sales Opportunities

KANTATA Quote

Acacium Group

202210-48455

00607000004QKXXAA4

202210-48455

Stage 4 Acacium Azure CSP migration Claranet to Ensono

Closed Won

3/31/2025

112650.4

New

Sales Opportunities

EIM Quote

DAX data :

ACCOUNT_NAME

SFDC_ENSONO_OPPORTUNITY

KANTATA_OPPORUNITY

EIM_OPPORTUNITY

SP_OPPORTUNITY

AGREEMENT_SIGNATURE_DATE

OPPORTUNITY_NAME

OPPORTUNITY_STAGE

CLOSED_DATE

Sum of AMOUNT_IN_USD

Sum of CHURN_IN_USD

SALES_INVOLVMENT

QUOTE_SOURCE

3M Company

202412-64440

3M CP3000 study for z16 to migrated to DG

Closed Won

########

Sales Opportunities

No Quote

3M Company

202412-64446

3M IBMi Hong Kong egress project and China project overage billing

Closed Won

########

587.25

Sales Opportunities

No Quote

3M Company

202503-65670

3M Mainframe Application Latency Risk Assessment

Closed Won

########

6250

Sales Opportunities

No Quote

Re: Card values depends on slicer selection

DineshArivu — Wed, 13 Aug 2025 08:17:37 GMT

Selva-Salimi Greg_Deckler

Snowflake query : Output - 490 row count
select * from datalake.dm_bss_oss.vw_opportunity_quote_source where sales_involvment='Sales Opportunities' and opportunity_stage='Closed Won' and closed_date between '2025-01-01'and'2025-06-30' and (amount_in_usd between '0' and '50000' or churn_in_usd between '-50000' and '0'); -- values 0,50000 and all are just a sample in this query but we have a separate slicers for the amount seelction in dashboard

DAX query : Output - 447 row count (should be 490)

CALCULATE (COUNT('Opportunity Quote Source'[SFDC_ENSONO_OPPORTUNITY]),

FILTER (ALLSELECTED('Opportunity Quote Source'),

(

'Opportunity Quote Source'[AMOUNT_IN_USD] >= MIN('Opportunity Quote Source'[AMOUNT_IN_USD]) &&

'Opportunity Quote Source'[AMOUNT_IN_USD] <= MAX('Opportunity Quote Source'[AMOUNT_IN_USD])

)

(

'Opportunity Quote Source'[CHURN_IN_USD] >= MIN('Opportunity Quote Source'[CHURN_IN_USD]) &&

'Opportunity Quote Source'[CHURN_IN_USD] <= MAX('Opportunity Quote Source'[CHURN_IN_USD])

)))

Re: Card values depends on slicer selection

Greg_Deckler — Wed, 13 Aug 2025 13:41:43 GMT

DineshArivu Again, have you tried it without the CALCULATE? See previous message.

Re: Card values depends on slicer selection

Anonymous — Tue, 19 Aug 2025 04:01:57 GMT

Hi ,Thanks for reaching out to the Microsoft fabric community forum.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything that is unrelated to the issue or question. Also please show the expected outcome based on the sample data you provided.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community

I would also take a moment to thank Greg_Deckler and Selva-Salimi, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.

Best Regards,
Hammad.

Re: Card values depends on slicer selection

Anonymous — Sun, 24 Aug 2025 19:27:11 GMT

Hi DineshArivu,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you can provide some sample data so that we can further help you with your issue.

If yes, you are welcome to share your workaround so that other users can benefit as well. And if you're still looking for guidance, feel free to give us an update, we’re here for you.

Best Regards,

Hammad.

Re: Card values depends on slicer selection

Anonymous — Fri, 29 Aug 2025 18:01:37 GMT

Hi DineshArivu,
Hope everything’s going smoothly on your end. As we haven’t heard back from you, so I wanted to check on you. Kindly share some sample data so that we can help you.
Still stuck? No worries just drop us a message and we can jump back in on the issue.

Best Regards,

Hammad.

Re: Card values depends on slicer selection

DineshArivu — Mon, 01 Sep 2025 09:16:44 GMT

Anonymous thanks for the followup .. yes it was fixed.

I have used a reference table and used [AMOUNT_IN_USD] & [CHURN_IN_USD] slicer from there.

post that the data matches and all ok now.

thanks again for your response 🙂