DIstinct Count of Category Name Filtered by One of Multiple Demand Columns
Hi everyone, I'm unable to get the correct distinct count of my product based on whether there is current demand, while I have two different demand columns which do not overlap in row entries. I provide simplified data in which I want it to be calculated like the following: As you can see, I want my Product column to be distinctly counted based on whether it is also populated by the Current Demand measure (this measure directly refencing a demand column in my data, since I want to create an explicit rather than implicit measure for Excel use). My thinking has been to create the following: Count=CALCULATE( DISTINCTCOUNT(ProductTable[Product],[Current Demand] <> BLANK()). However, it is not working. Please note that I don't have access to the most recent DAX functions such as DISTINCTCOUNTNOBLANK, so I hope there is still a way around this. Any help will be much appreciated.
Vlookup with duplicate values in both columns
Hey team, I feel the answer to my question does sit somewhere within this forum but my capability of understandig DAX limits my ability to combine several solutions into one. So here goes: I have some logic setup in an excel sheet that I'm trying to replicate into PBI but I'm struggling. Right now I have two tables that I'm trying to do a lookup between, initially I used "lookupvalue" but that doesn't like duplicate values. Now I'm using "firstnonblank" where I copied some formula from a forum that seems to work but I don't actually understand, so if you know of a solution please treat me like I'm a 5 year old because I'd prefer understanding the code rather than simply replicate. Table 1: lead data Basically individual records with contact details and a creation date. Email address is what I do to do my "vlookup" but creation date is also important as I want the most recent record in case of the email address occurring more than once. Table 2: Product purchases These are date stamped purchase records against email addresses. What I'm trying to achieve is a column in my lead data that gives me any desired column from the product purchases table for an email match for both the most recent lead and the most recent purchase. In this case I'm after product category for example. This is admittedly step 1 in a bigger string of verification so bonus points if it's possible to create 1 column for the penultimate result I'm after: I need a count of matches where the purchase date occurred AFTER the creation date as I'm trying to prove the indirect effect of some of our marketing campaigns with leads who don't DIRECTLY engage with us through submitting leads, do end up purchasing indirectly. I'll need several columns of table 2 such as category, sub category, and the actual days between lead creation and purchase so ideally looking for a vlookup esque function that can be applied to any column of choosing. If anyone can help me out with this that'd be greatly appreciated. Table 1 Email Creation date Desired output [email protected] 2020 A [email protected] 2019 B [email protected] 2019 null [email protected] 2019 null Table 2 Email Purchase date Product category [email protected] 2020 A [email protected] 2019 B [email protected] 2015 C
