Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi,
I have database with inventory values like below. (sample data)
Now, I want to create measure, which gonna calculate YTD value, but only for Items, which never had Transtype 0 or 9.
Hi @pawelj795 . May be like this? SUM = CALCULATE(SUM(Value),Transtype <> 0,Transtype<>9)
@Anonymous
That would be too easy 😉
I want to calculate YTD Value for items, which NEVER had TransType 0 or 9.
I mean by this, that some items could have all type of transactions (TransType) within a year.
Hi @pawelj795 ,
First you need to create a calculated column as below:
year = YEAR('Table'[Date])
It is to get the year of your date,then,you need a measure as below:
Measure =
CALCULATE(SUM('Table'[Visitors]),FILTER('Table','Table'[Trans_ type]<>0&&'Table'[Trans_ type]<>9))
Finally put your year column in the slicer, and measure in a card,and you will see :
You can choose any year you like to calculate the totalYtd value.
Here is my .pbix file,pls click here.
Best Regards,
Kelly
@v-kelly-msft
Nope, it's not what I'm looking for.
Look on screenshot with my real data.
In my report, I need to exclude Items with no TransType 0 or 9.
I don't want to only exlude transactions, but whole Item.
In above example, Item with number 1-2-0105-0052 should be exclude entirely from my matrix.
Hi @pawelj795 ,
First add a calculated column :
Column =
var a=SEARCH(0,'Table'[ItemID],1,0)
var b=SEARCH(9,'Table'[ItemID],1,0)
Return
IF(a=0&&b=0,1,0)
Then correct measure as below:
Measure =
CALCULATE(SUM('Table'[Visitors]),FILTER('Table','Table'[Trans_ type]<>0&&'Table'[Trans_ type]<>9&&'Table'[Column]=1))
For the related .pbix file,pls click here.
Best Regards,
Kelly
Hi @v-kelly-msft
Shouldn't be TransType instead of ItemID in below measure and also OR instead of AND?
Column = var a=SEARCH(0,'Table'[ItemID],1,0) var b=SEARCH(9,'Table'[ItemID],1,0) Return IF(a=0&&b=0,1,0)
Hi @pawelj795 ,
a=0 means there's no 0 in the column, so is b ,so a=0&&b=0 means there's no 0 or 9 in column ID, you said all the rows which contains 0 or 9 in column ID and TransType should all be excluded...so in my measure I have excluded all the posibilities.I have checked my result,it satisfies your need,where do you find not work?
Hi @v-kelly-msft,
I tried to do what I want in your file and it still doesn't work.
Look on shared pbix.
https://drive.google.com/open?id=1DpCDU2v4xJwMyeXjbFMCJX5RAFQEZg6N
ItemID's which I marked should be also on the table rightward.
Why they aren't?
@v-kelly-msft
Even with my above adjustments or with precisely your measure, it still only exlude transactions with trans type 0 or 9, not whole Index (ItemID)
up
up
User | Count |
---|---|
80 | |
74 | |
63 | |
61 | |
45 |
User | Count |
---|---|
108 | |
98 | |
89 | |
82 | |
61 |