none
DAX Help

    Question

  • Hi

    I want to calculate the Distinct count like the formula below but seems, I cannot use DistinctCount clause in RETURN like the below one. Can someone assist.

    GetDistinctCounts:= 
    VAR TempTbl =
        SUMMARIZE (
                    FILTER ( 'FactSales',  'FactSales'[Trans] = 1 ),
                     'FactSales'[BranchID],
                    'FactSales'[SubBranchID],
                    'FactSales'[BranchCatID],
                    "TransActive",  CALCULATE( VALUES('FactSales'[TransUNO]))            
                  )
            
    RETURN 
        DISTINCTCOUNT( TempTbl, [TransActive])

    Monday, May 14, 2018 11:40 AM

Answers

  • Hi Arnav1210,

    Thanks for your response.

    >>>Is it possible to create calculated measure instead of column for MaxTrans
    Yes,It is possible. But I Would suggest you to use calculated column for better understanding.

    Here you go:

    MeasureMAXTrans =
    maxx(FILTER(all(Table1),
        Table1[BranchCatID]=Max(Table1[BranchCatID]) && 
        Table1[BranchID]=Max(Table1[BranchID]) && 
        Table1[SubBrabchID]=MAX(Table1[SubBrabchID])),
        Table1[Trans])
    
    GetDistinctCounts = CALCULATE(DISTINCTCOUNT(Table1[NumValue]), 
        FILTER(all(Table1),Table1[Trans]=[MeasureMAXTrans] ))

    Please kindly mark all the helpful replies as answers. By doing so, it will benefit all community members who are facing this similar issues. Your contribution is highly appreciated.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, May 21, 2018 9:06 AM
    Moderator

All replies

  • I'm not sure why you are doing the SUMMARIZE() operation. I think the following expression should give you the result you are after and should perform better:

    GetDistinctCounts:= CALCULATE( DISTINCTCOUNT( FactSales[TransUNO] ), FactSales[Trans] = 1)


    http://darren.gosbell.com - please mark correct answers

    Tuesday, May 15, 2018 12:01 AM
    Moderator
  • Thanks Darren for your time...

    I have SQL like following one and I want to write DAX for the same. After executing following SQL statement, I am getting values in column sa.TransUNO and I want to create a measure to calculate the DISTINCT COUNT of sa.TransUNO

    SELECT sa.BranchId, sa.SubBranchId, sa.BranchCatId,sa.TransUNO,MAX(sa.Trans) As TransAg
     FROM [dbo].[FactSales] AS sa
    WHERE sa.CustomerID=45
    AND sa.DateKey BETWEEN '20170101' AND '20171231' 
    GROUP BY sa.BranchId, sa.SubBranchId, sa.BranchCatId,sa.TransUNO

    Tuesday, May 15, 2018 1:49 PM
  • Hi Arnav1210,

    Thanks for your response.

    >>>I have SQL like following one and I want to write DAX for the same. After executing following SQL statement, I am getting values in column sa.TransUNO and I want to create a measure to calculate the DISTINCT COUNT of sa.TransUNO
    In this scenario, you can just create a simple distinct count measure as below:
    GetDistinctCounts:= DISTINCTCOUNT( FactSales[TransUNO] )


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, May 16, 2018 2:07 AM
    Moderator
  • Actually I want to create equivalent DAX for the SQL statement which I had shared so even DAX should consider MAX condition which is there in SQL  
    Wednesday, May 16, 2018 8:06 AM
  • Hi,

    DAX is not like SQL, we can create equivalent DAX for some SQL statements. But that's not mean we can can create equivalent DAX for all SQL statements . You need to change your mind from T-sql,just think in "DAX" way(row context,filter context,calculate and so on),share sample data and expected results based on it, that's will much helpful. Actually, Both Dareen and willson's solution will work for you.

    Friday, May 18, 2018 11:12 PM
  • Let me rephrase my question, I am having following DAX query and snap below is showing the output of DAX. There is one column in the output snap named [NumVal]. I want to modify following DAX and want to add one more calculated measure to calculate the DISTINCTCOUNT of values present under [NumVal] column so here, new measure should show 4 value

    EVALUATE
    SUMMARIZE (
    	CALCULATETABLE (
    				'FactSales',	
    				Filter('Date', 'Date'[Year] = 2017),
    				Filter('Client', 'Client'[ClientId]=69)
    					),
    		'FactSales'[BranchID],
            'FactSales'[SubBranchID],
            'FactSales'[BranchCatID],
    		'FactSales'[NumVal],
            "MaxValues", CALCULATE ( MAX ( 'FactSales'[Trans] ) )
    		
    		 )

    Saturday, May 19, 2018 5:08 PM
  • Hi Arnav1210,

    Thanks for your response.

    Now I understand what you are after. Assuming you have below sample data:

    Please try to create a calculated column called MAX as below:

    Max := 
    MAXX(FILTER(Table1,
        Table1[BranchCatID]=EARLIER(Table1[BranchCatID]) && 
        Table1[BranchID]=EARLIER(Table1[BranchID]) && 
        Table1[SubBrabchID]=EARLIER(Table1[SubBrabchID])),
        Table1[NumValue])

    In the end, you can try to create the distinct count measure as below:
    GetDistinctCounts := 
    DISTINCTCOUNT(Table1[Max])


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, May 21, 2018 2:26 AM
    Moderator
  • Thanks Willson for your valuable time !!

    Let me share some sample data. So following 1st table (snap1) is representing my source sample data and I want to create measure to find the MAXX of "Trans" column values. I want to create calculated measure (and not column) because different rows may return as output depending on Slicers i.e Year and ClientId so I want to calculate this in run time. (as a measure).  

    Now once I find the MAX of Trans column, I am expecting output as showing in snap2. 

    After that, whatever rows will come, I want to calculate the DISTINCT COUNT of "NumValue" column so final output would be like snap3.


    Monday, May 21, 2018 7:20 AM
  • Hi Arnav1210,

    Thanks for your response.

    In this scenario, Please try to create a calculated column called MaxTrans and ISMAX as below:

    MaxTrans =
     maxx(FILTER(Table1,
        Table1[BranchCatID]=EARLIER(Table1[BranchCatID]) && 
        Table1[BranchID]=EARLIER(Table1[BranchID]) && 
        Table1[SubBrabchID]=EARLIER(Table1[SubBrabchID])),
        Table1[Trans])
    
    
    ISMAX = 
    if(Table1[MaxTrans]=Table1[Trans],1)

    Then create a measure called GetDistinctCounts as below:
    GetDistinctCounts = 
    CALCULATE(
                  DISTINCTCOUNT(Table1[NumValue]), 
                  FILTER(all(Table1),Table1[ISMAX]=1)
            )


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, May 21, 2018 8:22 AM
    Moderator
  • Thanks Willson !!

    Is it possible to create calculated measure instead of column for MaxTrans

    Monday, May 21, 2018 8:43 AM
  • Hi Arnav1210,

    Thanks for your response.

    >>>Is it possible to create calculated measure instead of column for MaxTrans
    Yes,It is possible. But I Would suggest you to use calculated column for better understanding.

    Here you go:

    MeasureMAXTrans =
    maxx(FILTER(all(Table1),
        Table1[BranchCatID]=Max(Table1[BranchCatID]) && 
        Table1[BranchID]=Max(Table1[BranchID]) && 
        Table1[SubBrabchID]=MAX(Table1[SubBrabchID])),
        Table1[Trans])
    
    GetDistinctCounts = CALCULATE(DISTINCTCOUNT(Table1[NumValue]), 
        FILTER(all(Table1),Table1[Trans]=[MeasureMAXTrans] ))

    Please kindly mark all the helpful replies as answers. By doing so, it will benefit all community members who are facing this similar issues. Your contribution is highly appreciated.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, May 21, 2018 9:06 AM
    Moderator