Card values depends on slicer selection

[DineshArivu]

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 

DK

[v-mdharahman]

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.

[v-mdharahman]

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.

[v-mdharahman]

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.

[DineshArivu · Accepted solution]

@v-mdharahman 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 🙂

[Selva-Salimi]

Hi @DineshArivu 

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

[DineshArivu]

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

[Selva-Salimi]

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.

[DineshArivu]

@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

0

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

0

587.25

New

Sales Opportunities

No Quote

3M Company

202503-65670

3M Mainframe Application Latency Risk Assessment

Closed Won

4/17/2025

0

6250

New

Sales Opportunities

No Quote

3M Company

202506-66602

006Qs00000RYqmRIAT

3M contract extension with commitment to digital services

Closed Won

6/26/2025

0

108333.33

New

Sales Opportunities

KANTATA Quote

Acacium Group

202210-48455

00607000004QKXXAA4

202210-48455

202210-48455

Stage 4 Acacium Azure CSP migration Claranet to Ensono

Closed Won

3/31/2025

0

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

########

0

Sales Opportunities

No Quote

3M Company

202412-64446

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

Closed Won

########

587.25

0

Sales Opportunities

No Quote

3M Company

202503-65670

3M Mainframe Application Latency Risk Assessment

Closed Won

########

6250

0

Sales Opportunities

No Quote

[DineshArivu]

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

        )))

[Greg_Deckler]

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

[Greg_Deckler]

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

[DineshArivu]

@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 

[Greg_Deckler]

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

[DineshArivu]

@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