Dax Measure return countrows with case statement (divide, substract)

[Lily780]

Hi Experts,

I have a truble with counting of rows in a measure. I have a table 

And this table has a relationship to Calendar table (Table.Order Date = Calendar.Date)

I have a table visualization with all columns except Inerval and I need to count rows with specific conditions.

CASE WHEN ( ABS(TO_NUMBER(Calendar [Year])-
( CASE WHEN TO_NUMBER(TO_CHAR(Table.Order Date,'MM')) < 4
THEN TO_NUMBER(TO_CHAR(Table.Order Date,'YYYY'))-1
ELSE TO_NUMBER(TO_CHAR(Table.Order Date,'YYYY')) END )) ) = 0
THEN 1
WHEN Table."Interval" > 0 AND MOD(( ABS(TO_NUMBER(:Calendar [Year])-
( CASE WHEN TO_NUMBER(TO_CHAR(Table.Order Date,'MM')) < 4
THEN TO_NUMBER(TO_CHAR(Table.Order Date,'YYYY'))-1
ELSE TO_NUMBER(TO_CHAR(Table.Order Date,'YYYY')) END )) ),
Table."Interval" )) = 0 THEN 1 ELSE 0 END

Could you help me with this calculation please?

[Lily780]

Hey @Anonymous ,

Thanks a lot for your answer and help!

But this is not the full logic of what I want to show. I have attached the file (link https://drive.google.com/drive/folders/1ntXXfaaK09z8GCHOOoyORNkfTcRwoEBa?usp=sharing ). I changed the data set a bit and you can see 2 columns in the table. 2023 and 2021.

These columns explain what I want to get.
These columns must be generated for each year from Order Year. I have about 30 years in my data and therefore it will be tedious to calculate each column in Power Query. At the same time, the dynamic calculation in Power Bi is impossible. The slicer should depend on the number of years in the Date Order

Thefore it is not enough for me to sum up total values (like calculate) based on the Interval to display a unique number of rows. Perhaps I need an iterative measure that can display the unique number of the string, and not the sum of the interval.

For example, for 2023, I should get not 6 rows with 1, but 15 rows and 11 of them with 1 (in Flag column).

For example, ID 187 has 2 values in table and two of them = 1,

So, I need to return 1 rows with Inetrval = 2 (sum of rows) and Flag =1

But for ID 18789006 and Order Date 04/07/2021 instaed of 3 rows I need to display 2:

Hopefully, my explanation is clear

Thanks

[Anonymous]

Hi @Lily780 ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create a measure as below:

Flag = 
VAR _selyear =
    SELECTEDVALUE ( 'Calendar'[Year] )
VAR _ordate =
    SELECTEDVALUE ( 'Table'[Order Date] )
VAR _interval =
    SELECTEDVALUE ( 'Table'[Interval] )
VAR _ordyear =
    YEAR ( _ordate )
VAR _ordmonth =
    MONTH ( _ordate )
VAR _diff =
    ABS ( _selyear - IF ( _ordmonth < 4, _ordyear - 1, _ordyear ) )
RETURN
    IF (
        _diff = 0
            || (
                _interval > 0
                    && IF ( _interval <> 0, MOD ( _diff, _interval ) ) = 0
            ),
        1,
        0
    )

If the above one can't help you get the desired result, please provide some sample 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.

How to upload PBI in Community

Best Regards