Forum Discussion

DineshArivu's avatar
DineshArivu
Icon for Helper I rankHelper I
1 year ago
Solved

Card values depends on slicer selection

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

DAX
  • DineshArivu's avatar
    DineshArivu
    1 year ago

    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 🙂

14 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    1 year ago

    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's avatar
      DineshArivu
      Icon for Helper I rankHelper I
      1 year ago

      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's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        1 year ago

        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's avatar
      DineshArivu
      Icon for Helper I rankHelper I
      1 year ago

      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's avatar
        Selva-Salimi
        Icon for Solution Sage rankSolution Sage
        1 year ago

        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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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.