DAX: Count where row value equals filter context

[greenguy2012]

[FONT=&quot]Hey there,[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]New to DAX so any help is appreciated. My goal is to understand how many customers cancel in a given sold date. Basically how many items did we sell in a given time period vs how many cancelled in that same period. [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]What I'm trying to do is count the number of rows where [Cancel Date] column values equal the Pivot Table Row Context Date [Sold Date] grouped by Month. Ex Grouped by Jan Sold Month, 10 cancelled in same Jan period[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Needed Pivot Table output:[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Sold Month. | Cancel Count[/FONT]
[FONT=&quot]- Jan. | 10[/FONT]
[FONT=&quot]- Feb | 15[/FONT]
[FONT=&quot]- Mar | 20[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Hopefully thats clear. Thanks in advance for your help![/FONT]

 

[greenguy2012]

Here is the formula I think it would be but it doesn't work

=CALCULATE([Count Cancelled],filter(all('Table'),[Cancel Month]=[Sold Month** this is the pivot table row sold month])

 

[VBA Geek]

Here is the formula I think it would be but it doesn't work

Hello, maybe post a sample of your model? like this it is difficult to give it a shot

 

[greenguy2012]

Hello, maybe post a sample of your model? like this it is difficult to give it a shot

Here is a link to the same csv file

https://drive.google.com/open?id=1JPOcN_sX3w9E44ICs47x7tOFqje1XXm_

Image link: https://drive.google.com/open?id=1mt78pS2VCdeT8T7lTdWWD6J_jxnNP6pk

 

[VBA Geek]

If you create an inactive relationship between Dates[Date] and Sales[Cancel Date] and an active relationship between Dates[Date] and Sales[Sold Date] then

Sales in Period is simply

=<br><span class="Keyword" style="color:#0070FF">COUNTROWS</span><span class="Parenthesis" style="color:#969696"> (</span> Sales <span class="Parenthesis" style="color:#969696">)</span><br>


Cancel in Period is:

=<br><span class="Keyword" style="color:#0070FF">SUMX</span><span class="Parenthesis" style="color:#969696"> (</span><br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">SUMMARIZE</span><span class="Parenthesis" style="color:#969696"> (</span> Sales, Dates[Month], Dates[Year] <span class="Parenthesis" style="color:#969696">)</span>,<br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">CALCULATE</span><span class="Parenthesis" style="color:#969696"> (</span><br><span class="indent8">        </span><span class="Keyword" style="color:#0070FF">COUNTROWS</span><span class="Parenthesis" style="color:#969696"> (</span> Sales <span class="Parenthesis" style="color:#969696">)</span>,<br><span class="indent8">        </span><span class="Keyword" style="color:#0070FF">USERELATIONSHIP</span><span class="Parenthesis" style="color:#969696"> (</span> Sales[Cancel Date], Dates[Date] <span class="Parenthesis" style="color:#969696">)</span><br><span class="indent4">    </span><span class="Parenthesis" style="color:#969696">)</span><br><span class="Parenthesis" style="color:#969696">)</span><br>

Notice that SUMMARIZE uses the active relationship whereas the measure inside SUMX uses the inactive relationship. This allows you to not display data for those months where you have cancellations but not sales.

 

[greenguy2012]

If you create an inactive relationship between Dates[Date] and Sales[Cancel Date] and an active relationship between Dates[Date] and Sales[Sold Date] then

Sales in Period is simply

=
COUNTROWS ( Sales )



Cancel in Period is:

=
SUMX (
SUMMARIZE ( Sales, Dates[Month], Dates[Year] ),
CALCULATE (
COUNTROWS ( Sales ),
USERELATIONSHIP ( Sales[Cancel Date], Dates[Date] )
)
)


Notice that SUMMARIZE uses the active relationship whereas the measure inside SUMX uses the inactive relationship. This allows you to not display data for those months where you have cancellations but not sales.

Thanks so much, this achieves a static version of what I am looking for.

If I wanted to drag say "Product Type" into the Pivot as a Column header, it looks like this doesn't work. Is there a way for the Summarize section to be dynamic based on what filter context is in the pivot table?

 

[VBA Geek]

Thanks so much, this achieves a static version of what I am looking for.

If I wanted to drag say "Product Type" into the Pivot as a Column header, it looks like this doesn't work. Is there a way for the Summarize section to be dynamic based on what filter context is in the pivot table?

Hello, is the product ID in the 'Sales' table? do you have a 'Products' dimension table? Could you share a more representative table ?