Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello all,
I have this model. F..., E... and C... tables are linked to T.... by Date fields.
C can have just 1 order (no_commande in red), F may have several partial invoices for the same order in different dates (no_commande.2 in red),
E may have several partial deliverys for the same order in different dates.
I need to now for each order how much has been delivered and not invoiced yet until Date_ selected (measure based in max selected date for T.[Date] from a slicer ) I made a calculated colums in C ( in bleu) with the following formula with real tables names:
Expedie_non_fact =
var t =
FILTER(F; F[no_commande.2]=C[no_commande] && F[date_facture]<=[Date_Selected])
return
var v =
FILTER(E; E[Commande]=C[no_commande] && E[DATE_DELIVERY]<=[Date_Selected])
return
sumx(v;E[Montant])-SUMX(t;F[Mont_fact])
*** My problem is when I choose [Date_selected] it does not really change my value in my Expedie_non_fact column.
I tried to do the same formula but using a measure but it does not show me this column to build my filter F[no_commande.2]=C[no_commande], it shows me just measures.
I would to know, by order ( C.[no_commande] ) how much has been shipped ( E.[Montant] ) but not invoiced (F.[Mont_fact]) ultil [Date_selected] and put all this in the same matrix this way
Where
Expédié Comm = C[date_expedi]
No Commande = C[no_commande]
Expédié Interal = C[Expedie_non_facture] (calculated column)
Thanks for your help
First, you should know that:
1. Calculation column/table not support dynamic changed based on filter or slicer.
2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result.
https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/
Second, for your case, you need a measure and need use ALLxxx Function in it to get it.
If you still have the problem, please share a simple sample pbix file and your expected output.
Regards,
Lin
Hi @v-lili6-msft , thanks for your answer
I did try your suggestion but is not working
Here's my sample
https://drive.google.com/file/d/1A3gL5xun6iU5TA-x6kNf8kS0_ctCHgL2/view?usp=sharing
I'm specting to have this
Thanks for your help
I have tested on your sample file, for your case, just need to add a dim order table and create a dim order table, and create the relationship with 'Order','Invoiced','Shipped' table as below:
Now when create the visual, please use [Order] field from dim order table.
here is sample pbix file, please try it.
Regards,
Lin
Thank you very much. it works as I spected
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |