Forum Discussion
First occurence with multiple conditions
Hi, I am new to DAX and would like to create a measure that produce a column called [Rookie year] to count the first occurence of an individual that purchased an item based on other variables.
those are the other conditions to be considered in order to be counted as first occurence :
- His client number wich is unique to him and stay's the same year after year is called [N certificat]
- In the column [Statut certificat] It as to be "Permanent"
- The date of [Date certificat] as to be within 5 years of the date of purchase wich is the column [Date emission permis] in order to count
So each [N certificat] as to appear only once in the column rookie year.
see picture below for a more visual look of an example table. the datas are not the real datas so the occurence dont match.
Ive fiddled with some of the formulas found in the forum but couldnt find one that I am able to translate to my needs.
Thanks very much !
Hi BenMagn ,
I created a sample pbix file(see the attachment), please check if that is what you want. You can create a measure as below to get it:
Rookie year = VAR _selncer = SELECTEDVALUE ( 'Table'[N certificat] ) VAR _mindep = CALCULATE ( MIN ( 'Table'[Date emission permis] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[N certificat] = _selncer ) ) VAR _tab = CALCULATETABLE ( VALUES ( 'Table'[N certificat] ), FILTER ( 'Table', 'Table'[N certificat] = _selncer && 'Table'[Date emission permis] = _mindep && 'Table'[Statut certificat] = "Permanent" && 'Table'[Date emission permis] - 'Table'[Date certificat] <= 5 ) ) RETURN IF ( _selncer IN _tab, 1, 0 )If the above one can't help you, could you please provide more raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:
How to provide sample data in the Power BI Forum
How to Get Your Question Answered Quickly
And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
3 Replies
Hope this is easier to understand ! thanks!
Hi BenMagn ,
I created a sample pbix file(see the attachment), please check if that is what you want. You can create a measure as below to get it:
Rookie year = VAR _selncer = SELECTEDVALUE ( 'Table'[N certificat] ) VAR _mindep = CALCULATE ( MIN ( 'Table'[Date emission permis] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[N certificat] = _selncer ) ) VAR _tab = CALCULATETABLE ( VALUES ( 'Table'[N certificat] ), FILTER ( 'Table', 'Table'[N certificat] = _selncer && 'Table'[Date emission permis] = _mindep && 'Table'[Statut certificat] = "Permanent" && 'Table'[Date emission permis] - 'Table'[Date certificat] <= 5 ) ) RETURN IF ( _selncer IN _tab, 1, 0 )If the above one can't help you, could you please provide more raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:
How to provide sample data in the Power BI Forum
How to Get Your Question Answered Quickly
And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
