Forum Discussion

gringo89's avatar
gringo89
Icon for Helper I rankHelper I
6 years ago

Count step stock at specific date

Hi,

Firstly, sorry for my english.

 

We want to restitute the number of document on each status by date.

My fact table likes :

usersDate eventType event
101/03/2020Sended
104/03/2020Recevied
106/03/2020Integrated
106/03/2020Finished
201/03/2020Sended
203/03/2020Received
302/03/2020Sended
310/03/2020Received
311/03/2020Integrated
315/03/2020Finished
405/03/2020Sended
406/03/2020Received
407/03/2020Integrated

The workflow document is : Sended, received, integrated and finished

i have 3 dimensions : date, user et type event. 

 

On each date, i want to know the stock on each type event :

Type events01/03/202002/03/202003/03/202004/03/202005/03/202006/03/202007/03/202008/03/202009/03/202010/03/202011/03/202012/03/202013/03/202014/03/202015/03/2020
Sended232121111000000
Received001222111211111
Integrated000000111122221
Finish000001111111112

 

I want to create a measure but i don't know the dax syntax which can do it.

 

Thanks for your helps

 

Need Help

4 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi gringo89 ,

     

    We can use the following steps to meet your requirement.

    1. add an index column for fact table in Power Query.
     

     

    1. And we need to create relationships between tables,
     

     

    1. Then we can create a measure to get the result,
    Measure =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            FILTER (
                ALLSELECTED ( 'fact table' ),
                'fact table'[Date event] <= MIN ( 'Dim Date'[Date] )
            ),
            'fact table'[users],
            "LastType",
            VAR maxIndex =
                CALCULATE (
                    MAX ( 'fact table'[Index] )
                )
            RETURN
                CALCULATE (
                    MAX ( 'fact table'[Type event] ),
                        'fact table'[Index] = maxIndex
                )
        ),
        [LastType] IN DISTINCT ( 'Dim Type event'[Type event] )
    )
) +0
     

     

    BTW, pbix as attached.


    Best regards,

     

     

    • gringo89's avatar
      gringo89
      Icon for Helper I rankHelper I
      6 years ago

      Hi v-lid-msft 

       

      thanks for you're help.

      I think that this approach can resolve my requirement.

       

      I use SSAS (2016 version) live connection and i have an error with 'IN DISTINCT' syntax. I don't understand... 

      • gringo89's avatar
        gringo89
        Icon for Helper I rankHelper I
        6 years ago

        Hummm.. the 'in' operator is not supported on sql server 2016 (compatibilty tabular 1200) 😞